All Forums

Share best practices, knowledge, successes and issues in Analytics, and discuss Analytical Applications.

You must login to post to the forums.
Question on calendar week Topic by adash-7422 08 Jul 2009

Hi,In sys_calendar.calendar the weeks start from sunday and end on saturday.But I have a requirement where the weeks should start on monday and end on sunday (irrespective of the year).Which means if the date is 2009-12-31 then its begin date should be 2009-12-28 and end date 2010-01-03.and the next week should start from 2010-01-04 and end on 2010-01-10 and so on.I wanted to know how we can ac

12287 views
1 reply, 11 years ago
Another Question regarding DBC.AMPUSAGE Topic by Random_Thought 02 Jul 2009

When usage is logged in AMPUSAGE, and we include the &D&H in the Account name, does the CPU and Disk IO get logged against multiple Days and Hours if the same unit of work spans that far?IE if I run a query that runs for 7 hours at 9 am, does it log an entry just for the 09070209 or 7 entries showing the CPU and disk io for those hours?Random

10471 views
1 reply, 11 years ago
dbc.ampusage Topic by rvp 01 Jun 2009

What is the unit for CPU time in dbc.AMPUSAGE?Is it seconds ?thanks,rvp

13913 views
2 replies, 11 years ago
customer segmentation Topic by vas_srini 22 Jun 2009

Hi,I have a daily aggregate table, which gives details about customer's activity - existing, new etc. There is a new requirement to identify customers who are "reactivated" within a certain time period. To pre-build an aggregate table for reactivation customers based on a specific time period is easy - say 45 or 60 or n # of days. This is easy..But there is a need to build an aggregate table to find all "reactivation" customers let us say in the last 2 weeks or last 3 weeks or last 3 days. This means that a dynamic list has to be prepared using daily level aggregation and it wont be a static table. Is there an easy way of building it in Teradata? I think in Oracle, we can use materialized views.Appreciate your response.Thanks,Vas

10491 views
1 reply, 11 years ago
fas load/Deadlock issue Topic by Manish Jain 30 Jun 2009

Hi,We use one of the ETL tool to load data in Warehouse. A simple process is as followA---------->flat file-------->C$_A---------Target A(Oracle) (T.Database) (TD)Now problem is we use fastload to load table C$ from a flat file. This is for sure that C$_A is access by only one user at a time.ETL jobs are failing coz of deadlock on table C$ when fastload try to access this table.I can ensure that none of the other user was accessing the C$ table during loadingAny clue will be highly appreciated.

9887 views
0 replies
Generate errors for intersection of queries Topic by az_maverick 30 Mar 2009

How do i generate a error ,when a set of records ina query do not intersect a set of records in another query.For eg,if I have a query ,select A,b from ABC INTERSECT select B,d from XYZI want to display all records in ABC which do not intersect the b,d in XYZ. What logic do I use ?Thank you,az_maverick

13212 views
2 replies, 11 years ago
To know roll access Topic by MDF 15 Jun 2009

Hi,I m new to teradata.I don't have access on DBC. Can anyone tell me, how come i will know which are the database have given me the access in Teradata.Thanks

10803 views
1 reply, 11 years ago
I have problems with error 1920.Service NCR Put Port Management Service PUTPortMgrSvc Topic by chuotchit 02 Jun 2008

I have problems with error 1920.Service NCR Put Port Management Service (PUTPortMgrSvc) failed to start.Verify that you have sufficient privileges to start system sevices.But I use Administrator account for Set Up PUT- Parallel Upgradge tool program.

31413 views
6 replies, 11 years ago
Remove '-' from a column Topic by Meenakshi S 11 Nov 2008

I have a phone number column which has records like '111-222-333' . I need to remove the '-' from each phone number. Any suggestions?

35544 views
6 replies, 11 years ago
C and Teradata Topic by rvp 28 May 2009

The environment is Unix, AIX.I am new to Teradata and C Program. Can someone tell me how to logon to Teradata from C Program, I need to write a C program to do some Table updates in Teradata. Can you please share some information/sample code on the same?Thanksrvp

10743 views
1 reply, 11 years ago
Views or Tables Topic by svyschd 27 May 2009

I have to extract data from 3 huge production tables which have the data at week level and needs to be transformed to day level. I need this data extract to do some additional processing based on it using a bteq script. Is it better to have the data extracted during the bteq script execution process or is it better to have a view created for these using the following sqls and get the data from the views during bteq execution process? Which one will give us better performance?query 1:-------- SELECT TBL1.SKU,TBL1.STORE,CAL.DT, SUM( CASE CAL.DAY_OF_WK WHEN 1 THEN TBL1.SAT_SH_QTY WHEN 2 THEN TBL1.SUN_SH_QTY WHEN 3 THEN TBL1.MON_SH_QTY WHEN 4 THEN TBL1.TUE_SH_QTY WHEN 5 THEN TBL1.WED_SH_QTY WHEN 6 THEN TBL1.THU_SH_QTY WHEN 7 THEN TBL1.FRI_SH_QTY END ) FROM TABLE123 TBL1 JOIN CALENDAR_DAY CAL ON TBL1.WEEKID = CAL.WEEKID JOIN ITEM_DESC ITM ON ITM.SKU = TBL1.SKU JOIN DEPT_TBL DEP ON ITM.DEPT = DEP.DEPT AND ITM.SUBCLASS = DEP.SUBCLASS AND ITM.LINE = DEP.FINELINE WHERE TBL1.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X') AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1 GROUP BY TBL1.SKU,TBL1.STORE,CAL.DT HAVING SUM( CASE CAL.DAY_OF_WK WHEN 1 THEN TBL1.SAT_SH_QTY WHEN 2 THEN TBL1.SUN_SH_QTY WHEN 3 THEN TBL1.MON_SH_QTY WHEN 4 THEN TBL1.TUE_SH_QTY WHEN 5 THEN TBL1.WED_SH_QTY WHEN 6 THEN TBL1.THU_SH_QTY WHEN 7 THEN TBL1.FRI_SH_QTY END ) <> 0; query 2:---------SELECT SLS.SKU,SLS.STORE,CAL.DT,SLS.REPORT_CODE, SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_QTY WHEN 2 THEN SUN_QTY WHEN 3 THEN MON_QTY WHEN 4 THEN TUE_QTY WHEN 5 THEN WED_QTY WHEN 6 THEN THU_QTY WHEN 7 THEN FRI_QTY END ) UNITS, SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_AMT WHEN 2 THEN SUN_AMT WHEN 3 THEN MON_AMT WHEN 4 THEN TUE_AMT WHEN 5 THEN WED_AMT WHEN 6 THEN THU_AMT WHEN 7 THEN FRI_AMT END ) RETAIL FROM POS SLS JOIN CALENDAR_DAY CAL ON SLS.WEEKID = CAL.WEEKID JOIN ITEM_DESC ITM ON ITM.SKU = SLS.SKU JOIN DEPT_TBL DEP ON ITM.DEPT = DEP.DEPT AND ITM.SUBCLASS = DEP.SUBCLASS AND ITM.LINE = DEP.FINELINE WHERE SLS.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X') AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1 GROUP BY SLS.SKU,SLS.STORE,CAL.DT,SLS.REPORT_CODE HAVING SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_QTY WHEN 2 THEN SUN_QTY WHEN 3 THEN MON_QTY WHEN 4 THEN TUE_QTY WHEN 5 THEN WED_QTY WHEN 6 THEN THU_QTY WHEN 7 THEN FRI_QTY END ) <> 0; query 3--------- SELECT INV.SKU,INV.STORE,CAL.DT, SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_OH_QTY WHEN 2 THEN SUN_OH_QTY WHEN 3 THEN MON_OH_QTY WHEN 4 THEN TUE_OH_QTY WHEN 5 THEN WED_OH_QTY WHEN 6 THEN THU_OH_QTY WHEN 7 THEN FRI_OH_QTY END ) OH_UNITS, SUM( CASE DAY_OF_WK WHEN 1 THEN SAT_OH_QTY * RETAIL_AMT WHEN 2 THEN SUN_OH_QTY * RETAIL_AMT WHEN 3 THEN MON_OH_QTY * RETAIL_AMT WHEN 4 THEN TUE_OH_QTY * RETAIL_AMT WHEN 5 THEN WED_OH_QTY * RETAIL_AMT WHEN 6 THEN THU_OH_QTY * RETAIL_AMT WHEN 7 THEN FRI_OH_QTY * RETAIL_AMT END ) OH_RETAIL FROM INVTBL INV JOIN CALENDAR_DAY CAL ON INV.WEEKID = CAL.WEEKID JOIN ITEM_DESC ITM ON ITM.SKU = INV.SKU JOIN DEPT_TBL DEP ON ITM.DEPT = DEP.DEPT AND ITM.SUBCLASS = DEP.SUBCLASS AND ITM.LINE = DEP.FINELINE JOIN DEPT_TBL DEP ON ITM.DEPT = DEP.DEPT AND ITM.SUBCLASS = DEP.SUBCLASS AND ITM.LINE = DEP.FINELINE WHERE INV.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X') AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1 GROUP BY INV.SKU,INV.STORE,CAL.DT;

10252 views
0 replies
Target Date issues in warehouse miner... Topic by umairulhassan 15 Jan 2009

I am using Target Date literal in Variable Creation analysis in Where Clause. But when I use same variable creation in Refresh Analysis, the Target Date does not change accordingly.Any suggestion...

12850 views
1 reply, 11 years ago
Problem in finding the latest record Topic by Arun sankar 18 Feb 2009

Hi,We are facing this problem in our project :There are 2 tables tableA and tableB. We need to fetch the code value from the latest row in the tableA which satisfies the join conditions between tableA and tableB. The query is below :SELECTA.CODEFROM DB1.TABLEA A,DB1.TABLEB BWHEREA.KEYFLD=B.KEYFLDAND A.FIELD1='001'AND A.IND1 = 'Y'AND A.CODE <> ' 'GROUP BY 1HAVINGA.INSERT_TS = MAX(A.INSERT_TS) ;But i am getting the error message "3504:Selected Non-Aggregate values must be a part of associate group' .I tried using a derived table to fetch the max timestamp and the key field by having the same query. then joining with the same source table to fetch the rows.SELECTSRC.CODEFROM DB1.TABLEA SRC,(SELECTCODE,KEYFLD,MAX(INSERT_TS) AS MAXTSFROM DB1.TABLEA A,DB1.TABLEB BWHEREA.KEYFLD=B.KEYFLDAND A.FIELD1='001'AND A.IND1 = 'Y'AND CODE <> ' 'GROUP BY 1,2) DERIVEDWHERESRC.KEYFLD=DERIVED.KEYFLDAND SRC.FIELD1='001'AND SRC.IND1 = 'Y'AND SRC.CODE = DERIVED.CODEAND A.INSERT_TS=DERIVED.MAXTS;The above query works fine. But i am not able understand whats wrong with the first query. Please help me understand the problem.Thanks,Arun

13280 views
2 replies, 11 years ago
Convert Decimal to Signed Numeric value Topic by subho 14 May 2009

Teradata Table structure:CREATE SET TABLE Table1,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( field1 SMALLINT NOT NULL, field2 DECIMAL(15,2) NOT NULL, field3 DECIMAL(15,2) NOT NULL), UNIQUE PRIMARY INDEX IXCSL1 ( field1 );Record in Teradatafield1 | field2 | field31 | -123456789.31 | 56745111.78Mainframe file Layout:01 field2 PIC -(12)9.01 field3 PIC -(12)9.Output mainframe file for the Teradata records should look likefield1 | field2blankspaceblankspace-123456789 | blankspaceblankspaceblankspaceblankspace56745112I am trying to extract records from Teradata to Mainframe using BTEQ XPORT.I have used various combinations of cast, format; I can think of. But I am not getting the desired result. Any help will be appreciated.

14315 views
2 replies, 11 years ago
Simple query but unable to find the syntax error Topic by pema_yozer 14 May 2009

I have edited the below query by addign the case statements in bolf but its giving me a syntax error,3706: Syntax error: expected something between the word 'STAT' and '.'. Can any one locate the issue with teh queryREPLACE VIEW xyz.xxxxxAS LOCKING ROW FOR ACCESSSELECT STAT.MI_MOF_GRP_NUM ,STAT.MMS_GSTAT_DATE ,STAT.EFF_STA_DTE ,STAT.CCY_NET_BAL ,STAT.STG_NET_BAL ,STAT.CCY_NET_FEE_BAL,CASE WHEN MGRP.MONITORTYPE = 4 THEN CASE WHEN STAT.AGGREGATELIMIT = 99999999999.00 THEN 9999999999.00 ELSE STAT.AGGREGATELIMIT END ELSE CASE WHEN STAT.NET_LIMIT = 99999999999.00 THEN 9999999999.00 ELSE STAT.NET_LIMIT END END AS NET_LIMIT,STAT.CCY_GROSS_BAL ,STAT.STG_GROSS_BAL,CASE WHEN STAT.GROSS_LIMIT = 99999999999.00 THEN 9999999999.00 ELSE STAT.GROSS_LIMITEND AS STAT.GROSS_LIMIT ,STAT.MMS_STAT_CHANGED,CASE WHEN STAT.AGGREGATELIMIT = 99999999999.00 THEN 9999999999.00 ELSE STAT.AGGREGATELIMITEND AS STAT.AGGREGATELIMIT ,STAT.CCYAGGBAL ,STAT.STGAGGBAL ,STAT.CCYSODAGGBAL ,STAT.STGSODAGGBAL ,STAT.SODBALDTE ,STAT.CCYSODNETBAL ,STAT.STGSODNETBAL ,STAT.CCYSODGROSSBAL ,STAT.STGSODGROSSBAL ,STAT.EFF_END_DTE ,STAT.DW_LOD_TMPFROMabc.def STATINNER JOINghi.jkl MGRPON STAT.MI_MOF_GRP_NUM = MGRP.MI_MOF_GRP_NUM;

11559 views
1 reply, 11 years ago
DEMOGRAPHIC COMPLIANCE in cLDM Topic by cremen 13 May 2009

I am not clear the definition in this entity "Designates the degree upon which a DEMOGRAPHIC is met. Examples:1 - Completely met,2 - Partially met,3 - Not met."Have anybody give me the example data or any cases to explode my imagination please?

10663 views
0 replies
Need to load a column from a header field. Topic by Priti Suresh 08 Apr 2009

Hi All,I have a file with 1 header record starting with 0. Detail records starting with 1 and trailer record starting with 9.We do a normal fload on the table loading the records. Now i need to introduce a new column which will get its value from the header field. char(1).How do i go about it?thanks,priti

11488 views
1 reply, 11 years ago
Unnamed procedure Topic by vk1 15 Apr 2009

I am new to teradata and wanted to knowIf the use of unnamed procedures in Oracle supported in Teradata? How do i go about it? Due to strict rules here, i am not allowed to use stored procs, so i need to know an alternate way to run my script which has some looping and conditional constraints.Any help?Thanks,vk

10486 views
0 replies
Date calculation Topic by s_tera 15 Apr 2009

The self join is between b is to fetch a set of values rather than the key1. ( select a.key1,a.key2,a.key_date,a.val1,a.val2,b2.key1 from a join b b1 on a.key1 = b.key1 join b b2 on b1.descriptors = b2.descriptors ) a1 join c where c.key1 = b2.key1 and c.key2 = a1.key1 and c.key_date = ( select max(c1.key_date) from c c1 where c1.key1 = c.key1 and c1.key2 = c.key2 and c1.key_date <= a1.key_date )qualifymin(c.value) over ( partition by a1.key1,a1.key2,a1.key_date ) > 0The above one performs badly due the max date calculation happening in the subquery. Is there anyway to do it better?I use that to get the date equal to/ or the last date when comparing with table a.

10990 views
0 replies
Fast Export Topic by AndrewLivingston 14 Feb 2008

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

51604 views
5 replies, 11 years ago
drop Statistics before recollecting Topic by ac_rahul@yahoo.co.in 09 Apr 2009

Do we need to drop Statistics before Re-Collecting Statistics on the same column for a table ?if yes pls let me know why!!!!

12126 views
1 reply, 11 years ago
Participate in 3rd Annual Data Miner Survey Topic by Karl Rexer 26 Mar 2009

Please participate in our 3rd Annual Data Miner Survey Survey Link: www.RexerAnalytics.com/Data-Miner-Survey-Intro2.html Access Code: SL4TA Please also help us spread the word to more data miners. This link and access code can be emailed to others.We think that this research will be of interest to all members of this group. A Research Summary Report will be free and available to all. Contact krexer@RexerAnalytics.com if you have any questions about this research, or to request a copy of last year's Summary Report.Thank you!

10815 views
0 replies
Teradata DBA position in Ashburn,VA and Atlanta,GA. Topic by Raad 25 Mar 2009

Hi!!I am writing to see if you are currently available for a position in Ashburn,VA. I am looking for a Teradata DBA for a 8+ months contract. If you or anyone you may know of is interested, please email me at your earliest convenience.Location: Ashburn,VADuration: 8+ monthsJob Description:At least 6 years software design/development experience. Teradata DBMS. Data Warehousing. Teradata data modeling Teradata certified master developer with minimum of 2 years experience in reporting database development (star/snow flake schema), adept in use of AJIs. Strong communication skills along with process oriented development backround are a must. Ability to Interact with business users in gathering requirement for business intelligence reporting Design and develop a database schema and surrounding support elements for meeting #1’s output (requirement) Support and enhance #3 for high performance of #2’s output(database) Bachelors degree or higher, preferably in Computer Science or related field.Please send resume with contact details, rate, location, availability status. Sincerely,RaadGlobal Resource Management, Inc. (GRMI)678 935 0437 (Direct)770 729 9222 (Fax)raad@grmi.netwww.grmi.net"GRMI- not just a name...A COMMITMENT TO EXCELLENCE"

10925 views
0 replies
self join Topic by svyschd 10 Mar 2009

table 1 id1 id2 date val1 val2 val3 val4a b 1-jan-09 1 10 1 8c d 1-Jan-09 2 7 0 0 e f 1-jan-09 1 1 2 6g h 1-Jan-09 4 4 8 16 i j 1-jan-09 3 6 6 12k l 1-jan-09 7 21 1 4m n 1-jan-09 6 24 2 8o p 2-jan-09 5 5 0 0q r 1-jan-09 1 11 3 33table 2id1 attribute1 attribute2 attribute3 attribute4 attribute5a 1 1 1 1 1c 1 1 1 1 1e 1 1 1 1 1g 2 2 2 2 2i 2 2 2 2 2k 2 2 2 2 2m 3 3 3 3 3o 3 3 3 3 3q 3 3 3 3 3I have to select id1,id2,date,val1,val2 from the table 1 where the val3 is greater than 0for the id1 with same attributes for the same day.For examplefirst 3 rows should be ignored as they the same attributes but 0 for id1: c for the same date.Then the 2-Jan transaction is not taken into account as it is a different date but the other items belonging to id1(m and q) are selected.g h 1-Jan-09 4 4 8 16 i j 1-jan-09 3 6 6 12k l 1-jan-09 7 21 1 4m n 1-jan-09 6 24 2 8q r 1-jan-09 1 11 3 33How do i do this. The table 1 has around 8 million records and table 2 has 500,000 records. The performance should also be good.

18178 views
6 replies, 11 years ago
Using Terdata ODBC trying to connect teradata Database through SQL 2000 Topic by Manoj Bandha 12 Mar 2009

I am sql 2000 user and trying to connect to Teradata Database . I tried to test with install the :1.Teradata 7.2 and Teradata ODBC 3.06 ,2. Teradata ODBC 3.05 and Teradata SQL Assistant 7.1.3.

12212 views
1 reply, 11 years ago

Pages