All Forums

For topics on Teradata’s Unified Data Architecture and associated enterprise-level features and products. Potential topics could include integrations in and around Teradata, Aster, and Hadoop as well as supporting technologies like Connectors, QueryGrid, Backup/Restore, Multi-Systems, and the Unity product suite (Director, Loader, Data Mover, Ecosystem Manager).

You must login to post to the forums.
Regarding Data Extraction....... Topic by lokhande.dinesh29 07 Jun 2007

Hi All,i want to know how can i extract data from teradata into CSV files. i want know any of such tool available in market?Thanks & Regards,Dinesh

32163 views
5 replies, 9 years ago
Trying to Identify Logons that are not assigned to a the person whom it belongs to. Topic by Bohica 19 Feb 2010

Trying to Identify Logons that are not assigned to a the person whom it belongs to. I want to generate a report to accomplish this. Any help would be great.

6320 views
4 replies, 9 years ago
Slowly Changing Dimensions Topic by hsyed44 22 Feb 2010

Hi,I get the basics of SCD's. Type 1 and type 2. I m just having trouble with how to implement the SQL. Could anyone please provide a SQL example as to how Type 1 and 2 SCD's would be implemented?THanks,

7895 views
1 reply, 9 years ago
Timezone conversion Topic by veerabomma99 20 Feb 2010

Hi,Can you please help me in converting the TIMESTAMP from GMT to PST.

6975 views
0 replies
Packed decimal format issue. Topic by pratap_kudupudi 15 Feb 2010

Hi All, I have an input file, where in the DECIMAL(7,2) is being sent in a Comp - 3 format.Along with the actual values, it has few null values also being sent across.I have tried to view the contents of the file using the cobol copy book and the null value looks something like X'00000000'(Hexa decimal reprsentation)ERROR: INVALID DECIMAL DATA DURING COMPARISON. Record 1 of Import 1 rejected due to preceding error.My Mload layout definition is .FIELD PRE_CNT_BK_VALUE * DECIMAL(7,2) NULLIF PRE_CNT_BK_VALUE= '00000000'; .But when i use the above sytax, none of my records are being considered for loading.Please suggest on how a Comp -3 NULL Decimal value can be converted to NULL.I am using the MLOAD utility.Regards,Pratap.

8185 views
2 replies, 9 years ago
Teradata Forums migration imminent Topic by Host 14 Feb 2010

Dear users,Per recent communication, we are pleased to announce the consolidation/merger of the Teradata Discussion Forums and the Teradata Developer Exchange Forums. The integration of the forums will create a single location - http://forums.teradata.com - for you to participate in Teradata Forums to exchange ideas with your peers and to get access to important Teradata information.We expect the migration to occur on the weekend of February 20th, 2010.We will be migrating across all legacy forum content, and will migrate your user account as well. For users that do not already have an account on Teradata Developer Exchange ( http://developer.teradata.com ), we will create an account using your existing Teradata Forums details, and will generate a new temporary password for you. These new account details will be emailed to you.Please watch for additional notices that will provide information on timing, accessing and using your forum account. To read more about the consolidation visit: http://developer.teradata.com/blog/neilotoole/2010/02/teradata-forums-migration-imminentThank you for your patience during this process, and we look forward to seeing you on the new Teradata Forums.- The Teradata Forums team

5697 views
0 replies
Teradata Forums migration imminent Topic by Host 14 Feb 2010

Dear users,Per recent communication, we are pleased to announce the consolidation/merger of the Teradata Discussion Forums and the Teradata Developer Exchange Forums. The integration of the forums will create a single location - http://forums.teradata.com - for you to participate in Teradata Forums to exchange ideas with your peers and to get access to important Teradata information.We expect the migration to occur on the weekend of February 20th, 2010.We will be migrating across all legacy forum content, and will migrate your user account as well. For users that do not already have an account on Teradata Developer Exchange ( http://developer.teradata.com ), we will create an account using your existing Teradata Forums details, and will generate a new temporary password for you. These new account details will be emailed to you.Please watch for additional notices that will provide information on timing, accessing and using your forum account. To read more about the consolidation visit: http://developer.teradata.com/blog/neilotoole/2010/02/teradata-forums-migration-imminentThank you for your patience during this process, and we look forward to seeing you on the new Teradata Forums.- The Teradata Forums team

5795 views
0 replies
Using Teradata host for both database and ETL? Topic by Dante 12 Feb 2010

When working with Teradata I have always used one host (mainframe or midrange) to control ETL and the teradata database was isolated on its own hardware where nothing else was running. Has anyone worked with ETL processes that actually run on the Teradata host itself? Is there a good white paper that talks about this and the pitfalls?Thanks!John

5799 views
0 replies
Stored Procedure Syntax - Variable Colon usage in Insert statements Topic by StephenHilton 08 Feb 2010

I apologize if this is a simple answer, but I was surprised today to find that the colon preceding a variable use in an INSERT statement was optional... Consider this example: CREATE TABLE Test_Variable_Syntax_Landing ( InsertType CHAR(10), caldt DATE, weekofmonth BYTEINT);REPLACE PROCEDURE Test_Variable_Syntax (asofdate DATE) MAIN: BEGIN DECLARE caldt DATE; DECLARE wom INTEGER; SELECT Calendar_Date, Week_of_Month FROM sys_calendar.calendar WHERE calendar_date = :asofdate INTO :caldt, :wom ; INSERT INTO Test_Variable_Syntax_Landing ( InsertType ,caldt ,weekofmonth ) SELECT 'With:' ,Calendar_date ,:wom --<--- With FROM sys_calendar.calendar WHERE calendar_date = :caldt ; INSERT INTO Test_Variable_Syntax_Landing ( InsertType ,caldt ,weekofmonth ) SELECT 'Without:' ,Calendar_date ,wom --<--- Without FROM sys_calendar.calendar WHERE calendar_date = :caldt ; END MAIN;CALL Test_Variable_Syntax (CURRENT_DATE);CALL Test_Variable_Syntax (CURRENT_DATE-7);CALL Test_Variable_Syntax (CURRENT_DATE-14);CALL Test_Variable_Syntax (CURRENT_DATE-21);CALL Test_Variable_Syntax (CURRENT_DATE-28);SELECT * FROM Test_Variable_Syntax_Landing ORDER BY 2,1;DROP TABLE Test_Variable_Syntax_Landing; Both INSERT statements seem to work just fine (on my V12). Is this accurate? Is there any practical difference?I guess I've always used colons everywhere.Thanks!Stephen

11547 views
0 replies
Improper column reference in the search condition of a joined table. PLZ HELP Topic by hsyed44 04 Feb 2010

SelectA.Column1,B.Column1Case When C.column1 is nulland C.column2 is nullthen 'y'else 'n'end flagfrom STG.table1 as A,STG.table2 as Bleft outer joinTGT.table1 as Con A.column1 = C.column1and B.column1 = c.column2Why am i getting this error??Improper column reference in the search condition of a joined table. please help.Thanks

12589 views
4 replies, 9 years ago
'Pre-Built' dimension Topic by marcmc 29 Jan 2010

I am trying to understand the concept of a pre-built dimension.The following DDL exemplifies my problem:-- Step 1 - Create the staging table.CREATE SET TABLE marc_centlook ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(Ge_lookup_id CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_source_id CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_lookup_code CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_lookup_desc CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_lookup_parent CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_lookup_key CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( Ge_lookup_id ,Ge_lookup_code );-- Step 2. Populate the staging tableINSERT INTO marc_centlook VALUES('RI_1', 'H', 'A1', 'EOL', '', '');INSERT INTO marc_centlook VALUES('RI_1', 'H', 'A2', 'Scrapped', '', '');-- Step 3. Create the the staging tableCREATE SET TABLE vht_su_risk_intelligence ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(Vht_risk_intelligence_id SMALLINT,Vht_risk_intelligence_code CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,Vht_risk_intelligence_desc CHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( Vht_risk_intelligence_code );-- Step 4. Populate the the staging tableINSERT INTO vht_su_risk_intelligence SELECT ROW_NUMBER() OVER (ORDER BY Ge_lookup_code) + 0,Ge_lookup_code,Ge_lookup_descFROM marc_centlook BCPWHERE NOT EXISTS(SELECT *FROM vht_su_risk_intelligence SURRWHERE SURR.Vht_risk_intelligence_code = BCP.Ge_lookup_codeAND Ge_lookup_id = 'RI_1')AND Ge_lookup_id = 'RI_1'----------------------------------------------------------------------------This gives me:1 A1 EOL2 A2 ScrappedI am looking to create:1 A1 EOL2 A2 Scrapped3 A1/A2 EOL/Scrapped 4 A1/A2 Scrapped/EOLOr...1 A1 EOL2 A1 EOL3 A2 Scrapped4 A2 Scrapped5 A1/A2 EOL 6 A1/A2 ScrappedThis needs to work with more than two values for example another row for above could be A3 NCBAny ideas the quickest and most elegant way to do this.

5989 views
0 replies
Install TTU on linux Topic by pwaltera 28 Jan 2010

Hi ... I'd like to install the Teradata Tools and Utilities v13 (specifically TPT and BTEQ) on a linux client machine that is different than my database machine. How do I go about this?Thanks.Walter

6727 views
0 replies
Teradata and Mainframe Topic by visakhcr 28 Jan 2008

HiI will starting to work with teradata in a mainframe environment soon. Can anyone share some material and tips about it?thx.

22855 views
3 replies, 9 years ago
Need help loading data Topic by monty4u1 20 Dec 2009

Hi Experts,I am new to Tera data development...I am trying to insert multiple files that are in csv format into one tera data table...The code mentioned below works file for one file at a time but I have 3 years of data in csv format for each individual day..I have to load n number of tables like this.I want to run the script mentioned below through our inhouse application and load all the data at once into the table.Can some one help me solve this.the data is in files with following name format.sales_graph_motel_level_20070429.csvsales_graph_motel_level_20070430.csvsales_graph_motel_level_20070501.csvsales_graph_motel_level_20070502.csvsales_graph_motel_level_20070503.csvThe last numbers in the name indicate the date. And I have to do this one time and we have data till dec 2009.Any help in really appreciated.Thanks in Advance.DATEFORM ANSIDATE;ERRLIMIT 50000000;TENACITY 4;SESSIONS 8;SLEEP 6;.LOGON HHY2/FASC_PORT_DCH,portbch;DATABASE PRODHOT;DROP TABLE PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STG;DROP TABLE PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ERR1;DROP TABLE PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ERR2;CREATE SET TABLE PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STG, NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT (REGION_ID varchar(20),DISTRICT_ID varchar(20),MONTH_ID varchar(20),WEEK_ID varchar(20),ADD_ID varchar(20),ADD_NM varchar(20),ADD_TYPE varchar(20),HOTEL_NAME varchar(20),TRANSACTION_DATE varchar(20),TAX varchar(20),PAIDEARLY_ROOMS varchar(20),PAIDLATER_DOLLAR varchar(20),PAIDEARLY_ROOMNUM varchar(20),PAIDEARLY_DOLLAR varchar(20),HIGH_SALE_ROOMS varchar(20),HIGH_SALE_ROOMDOLLAR varchar(20),MONTHLY_ROOMS varchar(20),MONTHLY_DOLLARS varchar(20),DATA_RESERVATIONS varchar(20),DATA_REVDOLLAR varchar(20),TOTAL_ROOMS_OCPvarchar(20),TOTAL_DOLLAR varchar(20),GAIN_DOLLAR varchar(20),CMD varchar(20))PRIMARY INDEX ( REGION_ID ,DISTRICT_ID ,MONTH_ID ,WEEK_ID ,ADD_ID ,ADD_NM ,ADD_TYPE ,HOTEL_NAME ,TRANSACTION_DATE ,TAX ,PAIDEARLY_ROOMS ,PAIDLATER_DOLLAR ,PAIDEARLY_ROOMNUM ,PAIDEARLY_DOLLAR ,HIGH_SALE_ROOMS ,HIGH_SALE_ROOMDOLLAR ,MONTHLY_ROOMS ,MONTHLY_DOLLARS ,DATA_RESERVATIONS ,DATA_REVDOLLAR ,TOTAL_ROOMS_OCP,TOTAL_DOLLAR ,GAIN_DOLLAR ,CMD );CLEAR;set record vartext ",";DEFINE F1 (varchar(100)) ,F2 (varchar(100)) ,F3 (varchar(100)) ,F4 (varchar(100)) ,F5 (varchar(100)) ,F6 (varchar(100)) ,F7 (varchar(100)) ,F8 (varchar(100)) ,F9 (varchar(100)) ,F10 (varchar(100)) ,F11 (varchar(100)) ,F12 (varchar(100)) ,F13 (varchar(100)) ,F14 (varchar(100)) ,F15 (varchar(100)) ,F16 (varchar(100)) ,F17 (varchar(100)) ,F18 (varchar(100)) ,F19 (varchar(100)) ,F20 (varchar(100)) ,F21 (varchar(100)) ,F22 (varchar(100)) ,F23 (varchar(100)) ,F24 (varchar(100)) FILE=\\IS02corp\rec\Files\Corp\IS\80720\HUB\HOTELSUMM\Logs\sales_graph_motel_level_20070429.csv;SHOW;BEGIN LOADING PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STG;ERRORFILES PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ERR1, PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ERR2CHECKPOINT 0;INSERT INTO PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STGVALUES(:F1 ,:F2 ,:F3 ,:F4 ,:F5 ,:F6 ,:F7 ,:F8 ,:F9 ,:F10,:F11,:F12,:F13,:F14,:F15,:F16,:F17,:F18,:F19,:F20,:F21,:F22,:F23,:F24);END LOADING;.LOGOFF;

7764 views
4 replies, 9 years ago
Data quality question Topic by hrmikwen 25 Jan 2010

Hello,Thanks for helping us on our last question. We have looked at different open source offers for data integration. And we are now looking for data quality software able to complete the data integration software. Are there any packages able to suit our needs? Different or same software makers are fine for us. Thank you.

5940 views
1 reply, 9 years ago
Conditional where clauses Topic by Radhika.Jakkula 27 Jan 2010

Hi,I want to implement an IF-ELSE logic in my where clause, some thing like below. I want the conditions in the IF-ELSE clause to be executed in sequence. I tried case statement also, but it doesnt work. Is there any way to implement such logic in Teradata? I am new to Teradata, hence any of your inputs would help me a lot.FROM F_SR_DMD_SALES_T DMD LEFT OUTER JOIN LKP_AMGEN_PRD_TO_PI_SLS_SPLIT LKPON DMD.MATERIAL_KEY = LKP.AMGEN_MATERIAL_KEY AND DMD.ICO_FIN_DAY_KEY_FIRST_DAY = LKP.ICO_FIN_DAY_KEY AND ( IF LKP.MEDICAL_CENTER_KEY NOT IN (-1, -2) THEN DMD.MEDICAL_CENTER_KEY = LKP.MEDICAL_CENTER_KEY AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY ELSE IF LKP.BRICK_MICROBRICK_KEY NOT IN (-1, -2) THEN DMD.BRICK_MICROBRICK_KEY = LKP.BRICK_MICROBRICK_KEY AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY ELSE IF LKP.TERR_CLASSIFICATION_KEY NOT IN ( -1, -2) THEN DMD.TERR_CLASSIFICATION_KEY = LKP.TERR_CLASSIFICATION_KEY AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY ELSE IF LKP.MEDICAL_CENTER_KEY IN (-1, -2) AND LKP.BRICK_MICROBRICK_KEY IN (-1, -2) AND LKP.TERR_CLASSIFICATION_KEY IN ( -1, -2) THEN DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY END IF)AND LKP.LOGICAL_DEL_FLAG = 'N'

9277 views
1 reply, 9 years ago
TPC_H benchmarks Topic by Rambazo 16 Jan 2010

Hello,I get block in editing the makefile and I need to do so to succeed to access a data base: TPC H benchmarks. I get blocked, I can not build the project. Can you explain to me what is a makefile? How to edit the makefile? How to make it? in Visual C++ 6any help is appreciate.RAMBAZO A.Master Degree Student Hunan University.Thanks.

8584 views
0 replies
BI software forums Topic by hrmikwen 13 Jan 2010

Hello, We will be settling for open source data integration software. We would need to have a thorough documentation and a forum able to help us on problems if we have any with our solution. Forums are always good to get solutions to problems. But need to be active in order to help. Do you know of any active forums for open source software?Thanks for your advice.

7874 views
1 reply, 9 years ago
how to set parameter to specify the exportfile format to unicode Topic by bchow 14 Jan 2010

In queryman, we can choose the 'save as' & specify the format of the file when we export the answerset manually, how to define the parameter in batch mode to set the output file as unicode format? I am using Teradata SQL assistance v7.0.0.

8269 views
0 replies
need help - list of 'constant' values in a join Topic by banan 07 Jan 2010

Dear all, i need to join a list of values with real table, likesel a1, a2, b2from tab1inner join (b1-1, b2-2b1-2, b2-2) tab2on a1=b1My problem is the 'definition' of tab2.The 'normal' Teradata sql for tab2:select b1-1, b2-1works fine - for only one pair of values.If i use union to have more than one pair of values, it needs a 'from' and a table name - because of the union.So i would need sg. like the DB2 sysdummy1 table - or another solution.I definitely can't create tab2 in the environment.

11195 views
4 replies, 9 years ago
Data Synchronization Topic by Gut 28 Sep 2009

Currently Teradata offers two products that address data synchronization directly, Teradata Replication Services (TRS) and Data Mover (TDM). TRS is a highly available option that deploys a 2PC when updates occur on tables with TRS defined on them.

9620 views
1 reply, 9 years ago
Difference between NOT and NOT IN Topic by AB75151 22 Dec 2009

Can you please help me with the difference between below queries.Sel * from TABLEwhere NOT (NAME= 'L'AND ID= '123')Sel * from TABLEwhere (NAME<> 'L'AND ID<> '123')I assumed that both should work in the same way.. But I observe that they dnt give me the same output. I am not sure how this works.Thanks!Prajith

10692 views
2 replies, 9 years ago
Droping Tables Topic by jeremyclover 23 Dec 2009

Have a procedure that's supposed to drop a table that it's pulling for a query. Here's what I have.BEGINDECLARE newcount INTEGER;DECLARE tablename1, tablename2 VARCHAR(200);DECLARE TableCur CURSOR FORSELECT tablename1,tablename2FROM database.viewFOR READ ONLY; OPEN TableCur; SELECT COUNT(*) FROM database.view INTO newcount; REPEAT FETCH TableCur INTO tablename1,tablename2; DROP TABLE :tablename1 ; DROP TABLE :tablename2 ; SET newcount = newcount - 1; UNTIL newcount = 0 END REPEAT; CLOSE TableCur;END;It's not liking the highlighted area. What's the issue with a drop statement that gets it's table name from a query?

8133 views
1 reply, 9 years ago
hello Topic by sunny07 28 Dec 2009

I am taking opinion from everyone out there. Who do you think is the first, second and third best web host out there.

7839 views
0 replies
Issue With Coalesce Topic by papvan 22 Dec 2009

Hi,Currently, I am having issue with Coalesce statement.I have 2 columns, a & b with datatype in database.While querying, I want to create a column; c Which is having value of a; if a is not having any value (is empty), need to use b; & if b is not having any value (is empty), need to leave it empty.Ex: 2 input cols with char(12) datatyperow a b1 9/22/2009 9/12/200923 9/24/2009 4 8/20/2007 I want the output as:row c1 9/22/200923 9/24/20094 8/20/2007 The Coalesce statement is taking only value of col a....if its empty, leaving the output empty.The below statements didn't worked:Coalesce(a, b, ' ') As c;Coalesce(CAST(a As char(12)), CAST(b As char(12)), ' ') As c;Please help me on this. Thanks

10562 views
1 reply, 9 years ago

Pages