All Forums

Topics related to the Teradata Database, excluding database connectivity (e.g. JDBC) and extensibility (e.g. UDFs) . Typical topics are about SQL syntax and usage (SELECT, GROUP BY etc), database performance, use of database functions, comparison with / migration from Oracle or DB2, and generally why the Teradata Database rocks. For articles, blogs and more, see the Database section of the site.

You must login to post to the forums.
DECLARE Topic by erics44 02 Sep 2016
/*com 021 mi*/


I am new to the forum so hello all :)
i am from a sql server background so just finding my feet with teradata
ive writen the above code and i get a Query is Invalid error? Can i not declare variables in teradata?

1 reply, 3 years ago
between condition make bad execution plan Topic by knowledge 01 Sep 2016



i have a query like this:


sel * from tbla a inner join tblb b on a.TheDay between b.SDay and b.EDay;


this is the execution plan:


2) Next, we do an all-AMPs RETRIEVE step from

     tbla by way of an all-rows scan with no condition into

6 replies, 3 years ago
How to identify tables that have been Block-level compressed using SQL Topic by sashi1985 17 Oct 2012 blc

Please can some tell me how to identify tables that have been Block-level compressed using SQL.
Hopefully from one of the DBC. <tables>.
Note: I do not have access to Ferret tool or anyother tools an admin has access to.

2 replies, 3 years ago
MAX SPOOL BY AMP Topic by Dinesh1975 28 Aug 2016

I am receiving the alert from viewpoint " Max spool by AMP".  60.19%.  Can you please let me know how to resolve it.

2 replies, 3 years ago
Using WITH Statement Modifier instead of Temp Tables? Topic by flsandman 19 May 2015 WITH Statement Modifier, Temporal Tables

I’ve run into some unknown territory here.  I have used a WITH statement modifier a number of times successfully.  I learned early on that the sequence is important when using two or three statements.  Now I have four WITH clauses I am using with each dependent on others.  I cannot get this to run no matter what sequence I place them in.  I’m sure I haven’t tried them all but I thought one of t

5 replies, 3 years ago
Single vs double quotes Topic by SGIT 31 Aug 2016

Hello, in the following code , if i replace the single quotes arounf 'credit card' with double quotes, it looks for a column with name 'credit card' . can some one throw some light on how teradata interprets double and single quotes


when appl_for_prod_typ = '30' then  'CreditCard'

when appl_for_prod_typ in ('12','13') then  'Homeline Plan'

1 reply, 3 years ago
Table A Left Outer JOIN Table B VS Table B Right Outer JOIN A Topic by Prashant_20986 31 Aug 2016 left outer join

Is Table A LEFT OUTER JOIN TABLE B same as TABLE B RIGHT OUTER JOIN TABLE A in in terms of output and performance?
(Regardless of data content and size of table A, and table B)

2 replies, 3 years ago
Replacing table Topic by SGIT 30 Aug 2016 replace table

In SAS if a new table A is created using the table A itself, the new table will overwrite the old. Can you pl let me know if same is the case in TD. -
create volatile table emp as
( select a.*
from emp a
left outer join company b
on =
with data and stats
on commit preserve rows

2 replies, 3 years ago
Set multiple variable in one case statement Topic by SGIT 30 Aug 2016

I am converting some SAS code to TD : In SAS you can set multiple variables within a single case statement. Can we do the same in TD? Thanks in advance

if RFRL_SRC_CD = '039' then do; 

  var_first = 1; 

  var_second = prcd_amt;


3 replies, 3 years ago
Updating second row value to new column first row Topic by ashokn1989 19 Aug 2016

I have a table
Id        rn            col1          col2         new_col
aa         1            592           650  
aa         2             750           800    
bb         1              250          600  
bb         2            300            null
output is
Id        rn            col1          col2         new_col

1 reply, 3 years ago
equivalent function for Month(); DateAdd() Topic by plechat 31 Aug 2016 month(), dateAdd(), last month

i want my sql script to automatically query the last month (first to last day) in the database.
Like if i am in august 2016 (the day doesn't matter), the query will pick all datassets with date between the 01/07/2016 and the 31/07/2016

1 reply, 3 years ago
Why does Teradata Express come only as a VM component? Why not as a standalone software to be installed on physical desktop? Topic by subhayu 30 Aug 2016

I have a basic question - Why does Teradata Express come only as a VM component? Why not as a standalone software to be installed on physical desktop?
Is it because the internal AMP-VPROC architecture or anything else? 

3 replies, 3 years ago
Formatting in Teradata Topic by hv186006 30 Aug 2016 sql, formatting

Hi guys,
New on Teradata. For a client I am trying to format some numbers. The wish is to paste the string 'EUR' to a value as follows.
EUR 0.32
EUR 0.00
EUR 189.56
EUR 1,234.87

4 replies, 3 years ago
Remote desktop to TD Express 15.10 Virtual Machine is not working Topic by juanalfonso 30 Aug 2016 #TeradataExpress15 #connection #Express #VMware

I'm using the TD Express 15.10 Virtual Machine but I've discovered that UltraVNC is not woking for doing a remote desktop from Windows.
Could anyone tell me how to fix it?
Thanks and regards

0 replies
Teradata: No more room in journal table for novaquality.journals Topic by juanalfonso 26 Aug 2016 Teradata Express 15.10



When I tried to insert more than a million of rows in a table, I got the following error:

  • No more room in journal table for novaquality.journals

I have a Teradata Express 15.10. About the space I have in the "novaquality" database:

3 replies, 3 years ago
moving first 4 characters to last 4 characters Topic by efgeorge18 29 Aug 2016

I have a field time_key (bigint) that shows '20160817'
I want to format it so that it displays as '08172016' in the sql.
I've played around with trim,cast,substr, can't seem to get it to work.
Any help is much appreciated.

2 replies, 3 years ago
ODBC TimeStamp Topic by wgfagundes 24 Aug 2016

Hi Guys,


It's my first topic here, I'm new on Teradata and we are facing a problem with ODBC with Timestamp. Let me explain the situation:


We have a Oracle GoldenGate running on IBM AIX replicating transactions to our Teradata 15.00. In our Teradata we have a Table that has a column with TIMESTAMP(6) WITH TIME ZONE NOT NULL.


2 replies, 3 years ago
Output is different in DBC.diskspace and for Dbc.diskspacev Topic by vc 29 Aug 2016

When I am firing the below 2 query,I am getting the same output as  abc_stg,test_mdl,123_srci for databasename columns
But when I replace Dbc.diskspacev with dbc.diskspace the output is blank.
I am trying to fetch the same output using dbc.diskspace

1 reply, 3 years ago
Filtering results of concatenation Topic by SQLfan123 28 Aug 2016 concatenation

Hi Everyone!
I'm having trouble with filtering concatenation results in Teradata. I'm using the following syntax "EXTRACT(YEAR from saledate)||EXTRACT(MONTH from saledate) AS date_num" to combine the year number with the month number for a variety of dates. This works great and the results are like the following "2005 4", "2008 9" etc.

2 replies, 3 years ago
extract time from timestamp(6) Topic by Kuldeepg92 25 Aug 2016

How to extract TIME from TIMESTAMP(6).
milliseconds are needed to get trimmed.
Thank you

1 reply, 3 years ago
How to transpose row values Topic by sasnai 29 Aug 2016

I have a data like
id    info
1     AB 1234 / CD 5678 / EF 9101.
I want output like this
id info
1 AB 1234
1 CD 5678
1 EF 9101
I tried using TD_UNPIVOT function but didnt get the required output.
Thanks in advance.

0 replies
Merge different rows in one row Topic by rychitre 28 Aug 2016

I have huge data as below. ‘Act_dt,cust_id and cust_line_id’ should be unique key.

1 reply, 3 years ago
PPI Topic by madhavi_kl 14 Feb 2007

hi alli read a statement saying " if a PARTITION BY is defined on a column other than a Primary Index column, then that table cannot have Unique Primary index defined on it. we can only have NON_UNIQUE Primary Index. and it may have secondary indexes."how can we justify this statement? if possible, kindly provide some example.

31 replies, 3 years ago
Sum distinct records in a table with duplicates Topic by txwylde 27 Aug 2016 sum, case, distinct

I have a table that has some duplicates. I can count the distinct records to get the Total Volume. When I try to Sum when the CompTia Code is B92 and run distinct is still counts the dupes.
Here is the query:

1 reply, 3 years ago
Inserting into table using multiple common table expressions CTE Topic by bmsouthern 27 Aug 2016

I'm wondering if someone has a workaround for performing an insert from a select statement inside of a CTE.  My CTE has multiple 'with' statements and from what I've been seeing, Teradata only supports an insert/select in a CTE with one 'with' statement.

1 reply, 3 years ago