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.
Storing TD Stored Procedures Topic by kokopelli_stew 13 May 2009

Hi, this is so silly... I've not done this in a while and must be making a very basic but not obvious mistake. I've written a stored procedure. I do understand that it has to be compiled to make it executable but my recollection is that simply CREATEing the procedure will do the syntax checks for me. I'm trying to store my procedure but continue to get the error message - "Failure 3706 Syntax error: Invalid SQL Statement." I've looked this up but apparently 3706 is a "catch-all" so that didn't help either. Trying to isolate the error, I've dumbed it down to the most basic syntax but the error remains. I'm hoping somebody can help me recall how to do this.CREATE PROCEDURE TESTIT ()beginend;THANKS!!

12954 views
3 replies, 7 years ago
Database Opinion Topic by lionel.raphael 28 Nov 2011

Folks, I am new to this field and trying to get a better understanding. I would like your opinion on what are the strengths and the weakness of Teradata's database solutions compared to Exadata.

7706 views
0 replies
Backup of Production tables to the DR server Topic by PriyaM 16 Nov 2011

Hi all,

 

7416 views
0 replies
hi i want to know now many hostnodes and hotstandby nodes in my environment is their any query to now from sql assistent Topic by vuduthala 16 Nov 2011

hi i want to know now many hostnodes and hotstandby nodes in my environment is their any query to now from sql assistent

please infrom me

 

6714 views
0 replies
find out the number of nodes in a teradata architecture Topic by Shefali 22 Feb 2006

How can you find out the number of nodes in a particular teradata database architecture? Is there any query that you can fire to find this out?

30093 views
3 replies, 7 years ago
distinct vs group by, insert vs create as Topic by barcoo 17 Sep 2009

HiFrom a processing/ efficiency point of view, looking at two questions below, which method in each question is faster?Consider this for an average sized table(whatever you think that is ha ha!)CONSIDER ALL OTHER FACTORS, INDEXES ETC THE SAME. Basically I simply want to know if there is a difference in speed with the way teradata handles each problem.1) Which is faster - Using DISTINCT or GROUP BY statement for the example belowSel DISTINCT NAME, DOB, AGEfrom table1orSel NAME, DOB, AGEfrom table1group by 1,2,3Which is faster/ more efficient (for an average sized table)?===========2) Which is faster - Using INSERT or CREATE AS statement for the example belowCREATE VOLATILE TABLE table1,NO FALLBACK ,NO BEFORE JOURNAL ,NO AFTER JOURNAL ,CHECKSUM = DEFAULT ( NAME VARCHAR(20) ,DOB DATE FORMAT 'YY/MM/DD' , AGE VARCHAR(3) ) PRIMARY INDEX (NAME) ON COMMIT PRESERVE ROWS;INSERT INTO table1SEL NAME, DOB, AGE FROM TABLE2orCREATE VOLATILE TABLE table1 AS(SEL NAME, DOB, AGE FROM TABLE2) WITH DATA PRIMARY INDEX (NAME) ON COMMIT PRESERVE ROWS;Which is faster/ more efficient (for an average sized table)?

21045 views
6 replies, 7 years ago
An ETL Tools comparison with reference to TERADATA database. Which ETL Tool is better ? Topic by vj_teradata 30 Nov 2009

Hi,We are looking for a comparison between any ETL Tool with regard to TERADATA database. We are trying to analyze ETL tools (Datastage, Informatica, Ab-Initio....so on) with respect to TERADATA database.This analysis is used to rate the ETL Tools in terms of performance along with TERADATA database.Could please help in making an analysis.

12934 views
2 replies, 8 years ago
EXISTS vs. IN Topic by iammai85 31 Aug 2007

Is EXISTS faster than IN? For example, table B has millions of records.Select A.student_id from Awhere EXISTS (select student_id from B where A.student_id=B.student_id)vs.Select student_id from Awhere A.student_id IN (select B.student_id from B)student_id is NOT NULL

94360 views
8 replies, 8 years ago
Date format Topic by ashija 19 Jun 2008

[font=Tahoma]Hi, I am getting a file which has a date value. But if the months or days are less than 10, then the date format will be M/D/YYYY (1/1/2008). If it is greater than 10, then the format will be MM/DD/YYYY (10/10/2008). I am trying to import the file data to a Teradata table.

28323 views
5 replies, 8 years ago
Bad format or character? Topic by papaya 28 May 2008

I am uploading some SAS files into TD, trying to convert string to decimal. I use (case when myvar is null then -999 else cast( myvar as decimal(18,0)) ) as idBut TD gives me an error, saying the column might contains bad character. Why this happened?

40740 views
5 replies, 8 years ago
Multiple Case Statements Topic by donviti 14 Oct 2011 decode, sas, working of case when then else in teradata

I've been working on this for hours!  Literally hours and I can't seem to get my statement to work.  I'm converting a decode statement. and I can get

one of them to work.  But I can't get them all to work the way they currently work in the Decode. 

 

54883 views
7 replies, 8 years ago
teradata appliance 2650 Topic by sunny.j 20 Oct 2011

Hello

 

Good Morning

 

 

any one explain me about the  new features of Teradata appliance 2650 in camparison with previous version .

 

6150 views
1 reply, 8 years ago
Stored Procedure Issue Topic by TommyP 12 Oct 2011

Hi,

I wrote the Stored Procedure below but when I call it, its getting error: CALL Failed. 7631:  MULTILVLBOM_COSTROLLUP_CURSOR:Fetch/Close attempted on a closed cursor in the stored procedure (MULTILVLBOM_COSTROLLUP_CURSOR).

 

REPLACE PROCEDURE MACH1_PROCS.MULTILVLBOM_COSTROLLUP_CURSOR

( IN IN_USER VARCHAR(50))

BEGIN

7434 views
3 replies, 8 years ago
Difference in output Topic by Dhanchik 30 Jul 2007

Hi All,Mentioned below are 2 queries .Can anyone have a look & tell me whether the output of both the queries would be same or not.Query 1SELECTS.ACCT_EVENT_TYPE_CD ,S.CUST_ACCT_EVENT_ID ,S.EVENT_USED_BY_CD ,S.EVENT_SEQUENCE_CD ,S.DW_AS_OF_DT ,S.CUST_ACCT_EVENT_CD ,S.EXT_EVENT_REF_CD ,S.EVENT_START_DT ,S.EVENT_END_DT ,S.AUDIT_USER_ID ,S.AUDIT_UPDT_TS ,S.DW_ACTN_IN ,S.DW_DATA_CHG_TS ,S.DW_LOAD_TS FROMIDW_STAGE.CUST_ACCT_EVENT_S S LEFT OUTER JOIN(SELECT ACCT_ID,CUST_ID,EVNT_USE_BY_CD,EVNT_SEQ_CD,MAX(DW_EFF_DT) DW_EFF_DTFROM IDW_DATA.CUST_ACCT_EVENT_TGROUP BY 1,2,3,4) TON((T.ACCT_ID=0 AND T.CUST_ID=S.CUST_ACCT_EVENT_ID AND S.ACCT_EVENT_TYPE_CD='C') OR (T.CUST_ID=0 AND T.ACCT_ID=S.CUST_ACCT_EVENT_ID AND S.ACCT_EVENT_TYPE_CD='A') ) AND T.EVNT_USE_BY_CD = S.EVENT_USED_BY_CDAND T.EVNT_SEQ_CD = S.EVENT_SEQUENCE_CDWHERES.DW_AS_OF_DT > COALESCE(T.DW_EFF_DT,date '0001-01-01')ANDS.DW_ACTN_IN in ('I','U')============================================================================Query 2SELECTS.ACCT_EVENT_TYPE_CD ,S.CUST_ACCT_EVENT_ID ,S.EVENT_USED_BY_CD ,S.EVENT_SEQUENCE_CD ,S.DW_AS_OF_DT ,S.CUST_ACCT_EVENT_CD ,S.EXT_EVENT_REF_CD ,S.EVENT_START_DT ,S.EVENT_END_DT ,S.AUDIT_USER_ID ,S.AUDIT_UPDT_TS ,S.DW_ACTN_IN ,S.DW_DATA_CHG_TS ,S.DW_LOAD_TS FROMIDW_STAGE.CUST_ACCT_EVENT_S S LEFT OUTER JOIN(SELECT ACCT_ID,CUST_ID,EVNT_USE_BY_CD,EVNT_SEQ_CD,MAX(DW_EFF_DT) DW_EFF_DTFROM IDW_DATA.CUST_ACCT_EVENT_TGROUP BY 1,2,3,4) TONT.ACCT_ID=0 AND T.CUST_ID=S.CUST_ACCT_EVENT_ID AND S.ACCT_EVENT_TYPE_CD='C'AND T.EVNT_USE_BY_CD = S.EVENT_USED_BY_CDAND T.EVNT_SEQ_CD = S.EVENT_SEQUENCE_CDWHERES.DW_AS_OF_DT > COALESCE(T.DW_EFF_DT,date '0001-01-01')ANDS.DW_ACTN_IN in ('I','U')UNIONSELECTS.ACCT_EVENT_TYPE_CD ,S.CUST_ACCT_EVENT_ID ,S.EVENT_USED_BY_CD ,S.EVENT_SEQUENCE_CD ,S.DW_AS_OF_DT ,S.CUST_ACCT_EVENT_CD ,S.EXT_EVENT_REF_CD ,S.EVENT_START_DT ,S.EVENT_END_DT ,S.AUDIT_USER_ID ,S.AUDIT_UPDT_TS ,S.DW_ACTN_IN ,S.DW_DATA_CHG_TS ,S.DW_LOAD_TS FROMIDW_STAGE.CUST_ACCT_EVENT_S S LEFT OUTER JOIN(SELECT ACCT_ID,CUST_ID,EVNT_USE_BY_CD,EVNT_SEQ_CD,MAX(DW_EFF_DT) DW_EFF_DTFROM IDW_DATA.CUST_ACCT_EVENT_TGROUP BY 1,2,3,4) TONT.CUST_ID=0 AND T.ACCT_ID=S.CUST_ACCT_EVENT_ID AND S.ACCT_EVENT_TYPE_CD='A'AND T.EVNT_USE_BY_CD = S.EVENT_USED_BY_CDAND T.EVNT_SEQ_CD = S.EVENT_SEQUENCE_CDWHERES.DW_AS_OF_DT > COALESCE(T.DW_EFF_DT,date '0001-01-01')ANDS.DW_ACTN_IN in ('I','U')=========================================================Is there going to be some difference in query due to Left Outer join.Probably the output would have been same I there would have been Inner join.Regards

48289 views
12 replies, 8 years ago
Unique Primary Index restricted in PPI Topic by jaismathews 26 Sep 2011

Hi All,

I was reading through a few Teradata material and came across this statement:

"Unique Primary Index cannot be defined when  the partition column is nor part of Primary Index" .

And my questions is : WHY ??? I checked with a few serior colleques and all of have the same question. Why ?

7712 views
2 replies, 8 years ago
Key Level Duplicates Topic by Ashok Pentapati 20 Jul 2011

Hi All,

I was trying to select all the columns which are not key level duplicates.For that I have written a query as

SELECT *
FROM Tab_a
QUALIFY ROW_NUMBER() OVER (partition by col1,col2 order by 1) = 1;

This gives the desired result.However job fails due to no more spool space error.Do you have any idea how to tune this query?

14037 views
13 replies, 8 years ago
How to remove non-numeric characters from a string? (Oracle TRANSLATE Equivlent function?) Topic by teradatashrini 27 Nov 2006

How to remove non-numeric characters (Spaces, brackets, alpha....) from a string? I am looking for a function (or a way) in Teradata similar to Oracle Translate function?Example: StrInput = "(800)555-1212 " StrOutput = "8005551212"StrInput = "1 800 555 1212" StrOutput = "18005551212"

31416 views
7 replies, 8 years ago
Colstats privileges??? Topic by SarathyG 17 Oct 2006

HiCould any one explain, what are the privileges(access rights) required to Collect Statistics on any Table?Also, which is the exact DBC table to look in to for obtaining all the privileges one user has on all the objects ?Thanks in adv.

34604 views
10 replies, 8 years ago
Tool for simulating DB load? Topic by nosrednAhsoJ 25 Aug 2011 performance, testing, application

We are looking to do some application performance testing and need to simulate a "real world" environment in which the warehouse is under strain.  Are there any tools/scripts out there that folks use to simulate the load typically placed on an EDW where is it generally pegged at 100% all day?

6361 views
0 replies
Case Statement Variable. Topic by marcmc 26 Mar 2008

Can a variable be used inside a case statement like so:SELECT (CASE WHEN Year_id = :v_Year_id THEN 1 END)FROM ......

20744 views
2 replies, 8 years ago
Numeric Overflow While Sum Topic by Ashok Pentapati 18 Jul 2011

Hi All,

I tried to to do sum for a column where I am getting numeric overflow error.

Eg:
----
Sel
Sum(Col1) from Tab1 group by1 col2;

Where Col1 is an integer.

I tried to caste it as

Sel
Cast(Sum(Col1) as decimal(38,0))
from tab1 group by col2;

But I am still facing the error.Can someone help me out?

7666 views
2 replies, 8 years ago
Dynamically update timezone. Topic by prasadnarmeta 14 Nov 2006

Hi,We deal with multiple sources and the data comes from different countries across the globe. I would like to store the data in one timezone say CET.Is there any way I can setup my database to accept all timezones, dynamically change the data to CET and store.

23012 views
4 replies, 8 years ago
not able to rename table while backups running Topic by Tdarc 01 Jul 2011 etl, backup, 7595

Hello All,
we are using Netbackup and TARA for backups .
ETL job was failed with RDBMS code 7595: Operation not allowed: an online archive logging is currently active on Database. my investigation is
while the backup job is running on the database it is holding lock on that database until it complete the backup for that database. Example CAPACC01 database takes around 50 minutes to
complte backup,during this time ETL jobs not abel do any change on tables in that database, etl jobs aborting with 7595 error. once backup job compltes same ETL jobs runs fine.

7967 views
0 replies
Join Index's PI data distribution Topic by jerome 30 Jun 2011 join index, index, distribution, amp functions

Hi ,

7503 views
1 reply, 8 years ago
AWT Topic by scn3957 13 Jan 2007

What is the best way to query for Amp Worker Tasks. (per amp)I'm trying to identify what are the amps that are running out of AWT.I'm greatful for any help.

30372 views
4 replies, 8 years ago

Pages