Teradata Forums / Analytical Applications / Teradata

Why subqueries in case are not working in teradata?

JACKSON — Thu, 08 May 2008 09:18:40 GMT

Please look at the query belowselect ename from emp e where not exists(select 'x' from dept d where d.deptno=case when d.deptname in (select deptname from xdepartments ) then 20 else 50 end );In the place of subquery "select deptname from xdepartments" , if I use hardcoded values like 100,200,3000 then it is working fine but I don't know why It is not working if I use subquery like above It's giving an error message..Please Help me out...I am using teradata SQL Assistant 7.1and getting Error message as : Illegal expression in when clause of case expression.Please help me outThanx Regards,jack

equivalent of rownum in td

JACKSON — Thu, 08 May 2008 09:05:07 GMT

Can you tell me what is equivallentof rownum in teradata in oracle I can useselect rownum,ename from empWhat is its equivalent query in teradata?

Date sequence with RANK() OVER

schabluk — Thu, 08 May 2008 06:17:47 GMT

Hi folks, I'm newbie to teradata, so please forgive me if I ask about something obvious ;)I have problem with SQL construction, for example, I have table with some data-------------- cut -------------- CREATE SET TABLE example, NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( PROD_ID VARCHAR (60) NOT NULL, INSTALLED_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL, PARENT_ID VARCHAR (60) NOT NULL);INSERT INTO example VALUES ('01', '2002-11-01', '100');INSERT INTO exampleVALUES ('02', '2002-12-01', '100');INSERT INTO exampleVALUES ('03', '2003-11-01', '100');INSERT INTO exampleVALUES ('04', '2003-12-01', '100');INSERT INTO exampleVALUES ('05', '2004-01-01', '100');INSERT INTO exampleVALUES ('01', '2002-11-01', '200');INSERT INTO exampleVALUES ('02', '2002-12-01', '200');INSERT INTO exampleVALUES ('03', '2003-11-01', '200');-------------- cut -------------- What I'm trying to achieve is horizontal sequence of two values: PROD_ID and INSTALLED_DATE. I wrote a query to do half of the job:-------------- cut -------------- SELECT S1.PARENT_ID, S1.PROD_ID, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING ) AS DATA0, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS DATA1, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING ) AS DATA2, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING ) AS DATA3, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 4 PRECEDING AND 4 PRECEDING ) AS DATA4, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 5 PRECEDING AND 5 PRECEDING ) AS DATA5, COUNT(S1.PROD_ID) OVER ( PARTITION BY S1.PARENT_ID ) AS TCountFROM example S1QUALIFY RANK() OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ) = TCount -------------- cut -------------- Maybe it is not very elegant, but it works, except that I don't have idea how to add sequence of PROD_ID columns to each DATAX column. The final result would be something like that:PARENT_ID|DATA0|PROD_ID0|DATA1|PROD_ID1|...|DATAN|PROD_IDNI would be grateful for any suggestions. Best regards.

How to import data from Excel file

milulove04 — Tue, 04 Dec 2007 20:48:19 GMT

Hi all,I need to import data from Excel file in Teradata.Can you help me?Thanks,Anthony

Facing error- '{' ('7B'X) is not a valid Teradata SQL token.

roy.kabir — Wed, 12 Mar 2008 23:42:53 GMT

Execution of a Win Demo TD (8.1) stored procedure using JDBC gives the following error-'{' ('7B'X) is not a valid Teradata SQL token.There is neither such token in the stored proc file nor in the table that is being used for dynamic value input. Though I saw a similar post with the invalid token as {'OA'X) instead of '7B'X, am at wits end trying to debug this one. Any input will be highly appreciated!Thanks,Kabir

EXcel to Teradata

nitinpatil — Thu, 17 Nov 2005 00:03:54 GMT

Hi ,

I have an Excel file with 1000 records ,i want to get these 1000 records to be loaded in teradata.Does any one know how to get this done and which utility will help me to get the excel file to teradata.

plz let me know !!!!!

regards
nitin

How to get pagination in Teradata..??

RRVARMA — Sun, 20 Apr 2008 10:02:26 GMT

How to get pagination in Teradata..?? Its possible in Oracle but is there anyway to get the required amount of exact records in Teradata...?? Actually i need to get first 50 records during my first call to the database and then the next 50 records during my second call.. how is tat possible in Teradata..?? Please help..

how to generate addition row as counter

nguyent02 — Fri, 21 Mar 2008 15:31:24 GMT

hi, i am needing help on how to create a table (No RAW data to insert) , only generate the count from 1 to 1000. the final table will Counter1...1000Thank you very much.

How to Split Output file using SQL?

jdeanz — Mon, 14 Apr 2008 07:47:40 GMT

Dear Experts,Is there any function to split the output file into difference files, irregarless the file format? i.e: By Row, by file size, by sampling, by attributesTo split every 1000th row of total 10,200 row. The output:11 Files (1000 row for each files + 1 file with remaining 200 row)To split by sampling of 20%,40% and 40% The output:File 1 - 20% of total DataFile 2 - 40% of total DataFile 3 - 40% of total DataMany thanks in advance.

Application development tools

NMadson — Thu, 10 Apr 2008 20:42:46 GMT

Can anyone suggest application development tools that work on top of a Teradata database, but which provide better debugging support than do Teradata stored procedures?I'm thinking of tools like those available within Visual Basic which allow you to step through code and examine the values that a variable has taken at each step.We've developed an analytic application using Teradata stored procedures, but as the application has grown, the debugging has become much more cumbersome. We have had to build in debugging logs, snapshots of global temp tables and a variety of other tools because they are not part of the basic Teradata development suite.I tried developing part of the application in VBA, but you have to build the SQL query to pass on to Teradata through the ODBC connection. That adds as much overhead to the development process as the manual debugging required by native Teradata stored procedures.TIA for any thoughts on the subject,Nolan

Getting one entry per RANK

carole — Fri, 18 Aug 2006 11:44:10 GMT

I need to get the most recent records (based on date) for each ID. I cannot do a group by since there are too many non-aggregate values that I do not want to consider when grouping. I have tried:
rank() over partition by ID,modification date order by modification date (DESC)

However, when the dates are the same for more than one record for a specific ID I get the same rank. I only need to pull one record per id and date when rank =1.

Thanks

Carole

Is anyone performing Statistical analysis on data using Teradata SQL

GETeraTiger — Fri, 28 Mar 2008 13:10:31 GMT

We have an application that is written in 'R' and was wondering if we could incorporate the calculations directly in Teradata SQL.We do Linear Algebra, matrix/vector calculations, Multidimensional Statisticsal Distributions, Random Number Generation, Numerical Optimizations and use algorithimy for correlation estimation simluation, aggregation, and disaggregation.Any one doing this in the Teradata environment?

Teradata certification material

yogesh — Mon, 10 Mar 2008 10:33:15 GMT

Hi to all,I am planning to do teradata certification. If any one knows abt this plz tell me. Where i will get books. I am planning to buy.thanks®ardspandu

Help Please, working with AVG

mior66 — Thu, 06 Mar 2008 14:53:46 GMT

Hi all,I am trying to do a statement equivalent to the following one from Oracle;SELECT DISTINCT A.EMPLOYEE_NBRFROM EMPLOYEE_POSITION A, EMPLOYEE_POSITION_CTRL BWHERE (SELECT AVG(SALARY) FROM EMPLOYEE_POSITION) > ((SELECT AVG(SALARY) FROM EMPLOYEE_POSITION_CTRL) * .05)+ (SELECT AVG(SALARY) FROM EMPLOYEE_POSITION_CTRL)OR (SELECT AVG(SALARY) FROM EMPLOYEE_POSITION) < ((SELECT AVG(SALARY) FROM EMPLOYEE_POSITION_CTRL)-(SELECT AVG(SALARY) FROM EMPLOYEE_POSITION_CTRL) * .05)Basically I am trying to determine if the AVG of salary in table A is within 5 percent of table B. It works for all other db's but in TD i get errors and i've tried different things with no luck. The datatype is Float. If somebody could help me i would sincerely appreciate it, were turning our wheels here. For more info or to contact me directly please respond to this post.

Retrieving varbyte column into a file

Dhanchik — Wed, 19 Mar 2008 03:05:39 GMT

Hi , What is the process of retrieving the column value whose datatype is varbyte in bteq script.For example I have a table ABC having a column bcd as varbyte(64000).In this column one .dat file is stored which contains some records.Now I want to retrieve the same column value into a file using the select queryselect bcd from abc;RegardsSanjay

Can a stored procedure be called thru a view ?

Madhavik — Wed, 12 Mar 2008 14:58:44 GMT

Hi,In Teradata , Can we call a stored procedure thru a view ( this is possible in oracle ) ?And pointers would be highly appreciated.Regards,Madhavi

Fast Export

AndrewLivingston — Thu, 14 Feb 2008 11:18:25 GMT

I have encountered some problems with Fast Export.Using a training script to train users on Fast Export and Fast Load, we run an export and then run the load.When we recently ran the export, the file that was output was a flat-file format, where the first column had 11 characters and the second column 6. This basically means that each column in each row is right-justified and padded with enough spaces to bring it up to the column length.Before, when I ran FastExport it output a tab-delimited file. I could then use this file to demonstrate FastLoad. Now, I would need to open the file up in Excel, and then save it as a tab-delimited, this left-justifies the text and puts the needed tab in.My question is how can I specify what delimiter FastExport is using?Here is the script:.logtable DATABASENAME.NAME_log;.logon BOX/USERID;.begin export sessions 4;.export outfilex:\export.txtMode record format text;Select prdid (char (11)) ,prdcd (char (6))From DATABASENAME.TABLENAME;.end export;.logoff;As I mentioned, the workaround was to open the output file in Excel, save it as a tab-delimited file and then try the fastload again, and it worked.I am ok on Fastload, as you can directly set the delimiter you will use, or use the DEFINE to set that up. I just can't understand why FastExport is saving as a flat-file.Any help would be appreciated!Thanks!Andrew Livingston

Convert Text to Proper Case is huge problme

nguyent02 — Thu, 06 Mar 2008 19:55:08 GMT

I need your help so badly, i have a requirement to convert text into proper case such as following:RAW data City Name to City NameWESTMINSTER CITY Westminster City (Upper case W and C)COSTA MESA CITY Costa Mesa City (Upper C, M, and C).Please teach me some trick here.... please.... Plese

Good software integration with Teradata and SAS ?

TimManns — Thu, 07 Feb 2008 17:50:31 GMT

I'm interested to know whether there is some software integration between Teradata and SAS. I am primarily concerned with one question; - Is there a mechanism to use SAS to process data directly within Teradata as auto-generated SQL ?or - would the raw data need to be extracted into a SAS system? Competitor products have had this functionality for many years now, and it is an important consideration for us to use our Teradata investment to its fullest.CheersTim

2nd Annual Data Mining Survey – Please Participate

Karl Rexer — Mon, 03 Mar 2008 11:07:54 GMT

2nd Annual Data Mining Survey – Please ParticipateRexer Analytics, a data mining consulting firm, is conducting our second annual survey of the analytic behaviors, views and preferences of data mining professionals. We would greatly appreciate it if you would:1) Participate in this survey, and 2) Tell other data miners about the survey (forward this invitation to them).Thank you. Your assistance with our “snowball sample methodology” is invaluable for reaching a wide and diverse group of data miners. In designing this year’s survey, we incorporated many suggestions from the excellent feedback we received from last year’s participants. If you participated in last year’s survey you will find that this year’s survey is a little longer, but we hope that the added length will pay off in significantly enriched results. Your responses are completely confidential: no information you provide on the survey will be shared with anyone outside of Rexer Analytics. All reporting of the survey findings will be done in the aggregate, and no findings will be written in such a way as to identify any of the participants. This research is not being conducted for any third party, but is solely for the purpose of Rexer Analytics to disseminate the findings throughout the data mining community via publication, conference presentations, and personal contact. If you would like a summary of last year’s or this year’s findings emailed to you, there will be a place at the end of the survey to leave your email address. You can also email me (krexer@RexerAnalytics.com) directly if you have any questions about this research or to request research summaries.To participate, please click on the link below and enter the access code in the space provided. The survey should take approximately 15 minutes to complete. Anyone who has had this email forwarded to them should use the access code in the forwarded email.Survey Link: www.RexerAnalytics.com/Data-Miner-Survey.htmlAccess Code: T59W2Thank you for your time. We hope the results from this survey provide useful information to the data mining community.Sincerely,Karl Rexer, PhD====================== Karl Rexer, PhD President, Rexer Analytics www.RexerAnalytics.com

Warehouse Miner

VSong — Fri, 22 Feb 2008 13:45:24 GMT

Our team is trying to use warehouse miner to do some fairly basic analytics and we're getting stuck (factor, cluster, etc). Can anyone recommend a good reference guide specific to this application. I've already checked out Amazon and didn't see anything that looked promising. Any and all help is much appreciated.

conversion of oracle code into Teradata

raj1983 — Thu, 21 Feb 2008 05:27:18 GMT

Hi, Please convert the following oracle code into Teradata...Thanks in advance.SET HEAD OFF;SET FEEDBACK ON;SET LINESIZE 32767;SET PAGESIZE 50000;PROMPT CAMPAIGN NM,CAMPAIGN CD,FLOWCHART NM,CELL CD,COUNTSELECT '"'||campaign_nm||'","'|| campaign_cd||'","'|| flowchart_nm||'","'|| cell_cd||'",'|| COUNT(*)FROM cop_unica_stage_itms aWHERE campaign_cd ='&campaign_cd'AND flowchart_nm ='&flowchart_nm'GROUP BY '"'||campaign_nm||'","'|| campaign_cd||'","'|| flowchart_nm||'","'|| cell_cd;

Reg different error codes and Return codes in Teradata

gururaj — Tue, 19 Feb 2008 10:48:42 GMT

Can any one please provide me list of Error codes and return codes in Teradat?Thanks,Gururaj

Equivalent of %TYPE and %ROWTYPE in teradata

raj1983 — Wed, 13 Feb 2008 02:16:34 GMT

Hello All,In oracle Both %TYPE and %ROWTYPE are used to define variables inPL/SQL as it is defined within the database. If the datatype orprecision of a column changes, the program automically picks upthe new definition from the database without having to make anycode changes.whats equivalent of this in Teradata.

Pricing of new SAS/ Teradata partnership

NMadson — Fri, 11 Jan 2008 05:30:11 GMT

Has anyone heard what the pricing will be for users of the new SAS/ Teradata partnership?Nolan

Basic diffrences between V2R5 and V2R6

LearnMore — Wed, 05 Dec 2007 02:33:43 GMT

Hi,Can anyone give the basic differences between V2R5 and V2R6.where can I get proper documentation regarding this.Thanks

SAS & Primary Indexes

mikejbritt — Tue, 20 Nov 2007 17:20:13 GMT

Does SAS have the capability to explicitly specify a PI as a result of a select statement the way you can in TD?If you can do this in TD:CREATE SET TABLE db1.table1 AS(SELECT name, ssn, addressFROM db2.customers)WITH DATAUNIQUE PRIMARY INDEX (ssn);How do you do that in SAS - or can you?

LogViewServer client application has been terminated abnormally

rejarakesh — Wed, 28 Nov 2007 11:22:10 GMT

Hi,I installed TPT on my server and running a test job but i am geeting following error.Teradata Parallel Transporter Export Operator Version 08.02.00.00W_0_o_Test1: private log not specified.Same test job is running fine on other server.Any one have any idea?Rakesh

WITH RECURSIVE

Rahul — Sat, 24 Nov 2007 07:43:47 GMT

create tabel --- CREATE SET TABLE RAHUL_DB.cbn_cls ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( cbn_cls_id INTEGER, cbn_cls VARCHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( cbn_cls_id );insert into rahul_db.cbn_cls values(3,'J30Y175');insert into rahul_db.cbn_cls values(4,'Y50');insert into rahul_db.cbn_cls values(6,'J12Y102');insert into rahul_db.cbn_cls values(7,'J30Y224');insert into cbn_cls values(9,'Y121');insert into cbn_cls values(10,'P11J55W33Y111');insert into rahul_db.cbn_cls values(8,'J35Y205');WITH RECURSIVE cbn_cls_tmp (cbn_cls_id,cbn_cls,cbn_ty,cbn_val,str_len) AS(SELECT cbn_cls_id, cbn_cls, Substr(cbn_cls,1,1) As cbn_ty1, Substr(cbn_cls,2,1)|| Case When Substr(cbn_cls,3,1) NOT IN ('J','P','W','Y') Then Substr(cbn_cls,3,1)|| Case When Substr(cbn_cls,4,1) NOT IN ('J','P','W','Y') Then Substr(cbn_cls,4,1) Else '' END Else '' End As cbn_val , Character(cbn_val)+2 As str_len From cbn_cls UNION ALLSELECT T.cbn_cls_id, Substr(T.cbn_cls From T.str_len) As cbn_cls2, Substr(cbn_cls2,1,1) As cbn_ty2, Substr(cbn_cls2,2,1)|| Case When Substr(cbn_cls2,3,1) NOT IN ('J','P','W','Y') Then Substr(cbn_cls2,3,1)|| Case When Substr(cbn_cls2,4,1) NOT IN ('J','P','W','Y') Then Substr(cbn_cls2,4,1) Else '' END Else '' End As cbn_val2 , Character(cbn_val2)+2 As str_len2 From cbn_cls_tmp T,cbn_cls R Where R.cbn_cls_id = T.cbn_cls_id)sel *From cbn_cls_tmp;i got an error at " Substr(T.cbn_cls From T.str_len) " this line when i try to extract cbn_cls value from the T.str_length poastion and also unable to get the RECURSIVE querythe out put i requie like --------------------------------cbn_cls_id--cbn_cls------cbn_ty1----cbn_val--str_len1----------P14J64Y265-------P--------4--------41----------J64Y265----------J--------64-------4 1----------Y265-------------Y--------26-------52----------Y36--------------Y--------36-------43----------J30Y175----------J--------30-------43----------Y175-------------Y--------175------5

Sampling by Percentage

Benjamin See — Thu, 22 Nov 2007 22:47:37 GMT

Dear who know,Currently I had a query to take 1000 sample from a customer table like this:------------------------------------Select Cust_Id, Name, Age From CustomerSample 1000------------------------------------What happen if I need to random sampling by percentage (let said 25%) from the customer table?How to write the SQL?Thanks! ^_^

About PE

Praneet Kulkarni — Thu, 22 Nov 2007 09:28:47 GMT

1)Can PE considered as Virtuall AMp?if so ,then , return Hashamp() includes PE also.2)How to invoke PDEConfig Utility?

Regarding Space Management In Teradata

Praneet Kulkarni — Mon, 19 Nov 2007 08:00:23 GMT

I am unable to Create the Table is Edwtest_Ads_Temp .but it is allowing to insert the new rows. I have checked the space .Still 879 Mb Space is available.And even no amp is full.I have used following queries to get the conclusion.1)sel sum(maxpermspace),from DBC.DataBaseSpace where Databaseid='00007408'XB;( '00007408'XB is Database id for the database I am referring) Result:=2122317760.00 Bytes 2) sel sum( currentperm) from DBC.TableSize where databasename='Edwtest_ads_Temp' ;Result= 1200550400.00 BytesSo Sum Of Current Perm Space is Less than Maxpermspace3)sel max(currentperm) from DBC.TableSize group by vproc where databasename='Edwtest_ads_Temp' ;In above query Max Of currentpermspace of each amp is less than the Maxpermspace/80(80 is number of amps)Amp is full only when it reaches =26528972=2122317760.00(Max Perm Space)/80Amp number which is occupied highest space is 50 I.e. 10132992.00 Bytescould you please clarify

Amp Sharing

LearnMore — Mon, 19 Nov 2007 00:02:49 GMT

Hi All,Let me know whether we can share the amps across different platforms.If it is so, how can we do that(mean the process)Thanks

Ip address of Server

Praneet Kulkarni — Mon, 19 Nov 2007 08:03:03 GMT

Is there any data Dictionary tables which maintains server name and Ip address of the server

What, no REPEAT(X,n) function for Teradata?

mcrad — Mon, 19 Nov 2007 14:27:54 GMT

Hello,I wants to log some simple ascii bar charts.e.g.5 *****6 ******9 *********3 ***2 **1 *every other db has either a REPEAT, REPLICATE, RPAD funtion to get the job done.any suggestions for TD?

Date Validation

John Abraham — Sat, 17 Nov 2007 00:05:35 GMT

Hello all .. In one of our processing, where we read from some tables and inserting into a new table, we have a CHR_CD and a CHR_VAL_CD field, both are defined as CHAR fields. If the CHR_CD field is 'RETIREDT', then we want to validate that the CHR_VAL_CD is a valid date. If not, we want to populate a default value in the field. How do I do that ?? Can it be part of the insert or does it have to be a separate update step ?? We do not want to abend the code in any case. Thanks in advance ..

ANSI vs. Teradata SQL

Rahul — Thu, 15 Nov 2007 12:02:47 GMT

hello all,can any 1 help me with the differences between ANSI vs. Teradata SQLamy meterial which can help me out

Version Related Info

LearnMore — Wed, 14 Nov 2007 02:28:51 GMT

Hi,let me know if nay table is present in dbc that holds the version related information abut the current version that we are using.thanks in advance

TD FSLDM

Nisith Bhattacharya — Sat, 10 Nov 2007 03:30:01 GMT

How do you relate an Account_num to the GL SEGMENT CD or GL PRODUCT CODE.

string surch problum

Rahul — Thu, 08 Nov 2007 03:18:59 GMT

hi i have a requirement like as column1---------ADJEIWadfrrt12i#()e fttg213233and i want to select all the rows which are not having A to Z any where in the attribute, spaces special char numbers can comei am using substr and retriving each char and checking with a NOT IN ('A','B','C',.....,'Z') condation is there any short cut to do