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.
How to recover from error 3916: Requested information not in dictionary Topic by maestrox2 1 day ago Error3916

Hello,
I have a probelm that I have been banging my head trying to recover from.
We have an application that replicates data from our own database into Teradata. Tests are run which repeatedly will create sets of tables, load data, perform some tests, then drop the tables.

19 views
1 reply, 20 hours ago
Equivalent sentence in Teradata for the Oracle "CONVERT"? Topic by juanalfonso 2 days ago

Hello everybody,
I can't find a sentence to make the same as "CONVERT" does in Oracle. For example:

56 views
4 replies, 1 day ago
DECODE Does Not Trap Divide by Zero Error Topic by ssi112 2 days ago

I've been beating my head against Teradata's wall this week and cannot figure out why this isn't working. We use this code in Oracle and recently ported data over to Teradata. The CASE and DECODES are as is from Oracle, however, unlike Oracle I get 2618 invalid calculation, division by zero.
Why does this work in Oracle and not teradata?

21 views
3 replies, 1 day ago
RDBMS error 3813: The positional assignment list has too many values Topic by 31_resu 2 days ago

Hello, I'm building a TPT script to load a flat file to a table. I'm running it from the command line:
tbuild -f c:\some_dir\tpt_load.txt
My Teradata platform system is: VERSION    14.00.07.15 RELEASE    14.00.07.16

44 views
2 replies, 1 day ago
Question about "regexp_replace" Topic by juanalfonso 1 day ago

Hello to everyone,

When I execute this sentence:

  • select REGEXP_REPLACE('XXX A1 A2 XXX', '.*( )(A[0-9]).*', '\2', 1, 1, 'i');

I get the second "(A[0-9])" expression, that is, "A2", instead of the first one ("A1").
I would like to know why this is like that and how to get "A1".
Thanks in advance and best regards

22 views
1 reply, 1 day ago
Alternative to Oracle's packages Topic by juanalfonso 1 day ago

Hello to everybody,

I'm starting to carry out a migration from Oracle PL-SQL to Teradata Stored Procedure Language and I can't find how to declare my own packages.

I've already seen a post talking about this where they say that there is not an equivalent to Oracle's packages in Teradata.

13 views
0 replies
How to define an array of varchars in Stored Procedure Language? Topic by juanalfonso 1 day ago

Hello to everybody,

I'm studying a migration from Oracle PL-SQL to Teradata Stored Procedure Language and I'm having problems trying to find a way to declare an array of varchars inside procedures/functions.

10 views
0 replies
Spool Space Issue on More Projected Columns Than Lesser Projected Columns Topic by Win 3 weeks ago spool space, spool space error, Projected Columns

What could be the cause of this?
I have the following two queries with similar source views and joins:
FIRST QUERY
The first one has more columns projected but is not returning a spool space issue: (EXPLAIN plan provided below - 5 steps)
SEL      D.PrimaryIndex

66 views
5 replies, 1 day ago
Automate my Teradata queries Topic by prateek.saxena 24 Nov 2013 automate

Hi,
 
I have some teradata codes which I want to automate. It should be done in a way that they can execute themselves on a specific date/time of a month. 
Any help will be appreciated.
 
Regards,
Prateek

8397 views
8 replies, 1 day ago
How to compare rows among different columns using particular scenario Topic by Gaurav2512 1 week ago @dnoeth

I have one table A which consists of two columns Route_Cd and Event_Actual_Dttm.
And other table B contains column as Src_Obj_Id

1)If Route_Cd and Event_Actual_Dttm are same for all Src_Obj_Id's which are coming from Table B
then we have to set flag as zero.

32 views
2 replies, 1 day ago
Column with keywords found in comments field Topic by harrytuttle 2 days ago

At work I've been given a task that I'm told is "simple and straightforward", but I'm having difficulty with:

13 views
1 reply, 1 day ago
Patitioning expression Topic by Abhishektd 1 day ago sql, partitioning

Which of the following partitioning expression has a better performance while SELECT, INSERT operations ? Consider the table has data around couple of Terabytes.
PARTITION BY RANGE_N(LOAD_TIMESTAMP  BETWEEN TIMESTAMP '2015-01-01 00:00:00.000000' AND TIMESTAMP '3499-12-31 23:23:59.999999' EACH INTERVAL '1' DAY );
or

5 views
0 replies
BTEQ: If Else condition Topic by SwapSays 1 day ago bteq, if else, .if
19 views
1 reply, 1 day ago
Find Only Duplicates Topic by abhijeetgupta29nov 2 days ago Find Duplicates

Hi All,
I have very big table and it has around 250 columns and this table may have duplicates.
this table does not have any UPI or USI or event primary index also based on many columns.
Now my requirement to find the duplicate so tell me what is the best way to find the dups.

23 views
2 replies, 1 day ago
Teradata SQL Assistant going to Not responding state while querying a table Topic by MeenakshiM 1 day ago

Hi,
When I am querying a particular table, Teradata SQL assistant is going to not responding state . Table record count is 19K
Table structure :
CREATE SET TABLE <TABLENAME> ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

9 views
0 replies
Logon credentials in BTEQ script not working Topic by DZanke 3 days ago

I am relatively new to Teradata and am having issues w/ creating a BTEQ script which will logon and export data. The issue is w/ the logon command, I cannot get it to recorgnize my password. I have no problem w/ executing an import script w/ FastLoad that has the credentials embedded, but BTEQ won't recognize from script or interactive mode. We are using teradata version: 14.00.07.12 .

34 views
3 replies, 1 day ago
How to modify datatype of a column using alter table command Topic by haran 06 Sep 2006

Hi,Can anyone help me reg thisEx:i am having a table Empcolumns:Empname varchar(10)Empno integerSalary decimal(10,2)i want to change the salary decimal (10,2) to decimal (15,2) using alter table command.IF i already have recors in that table can i modify that.Regards,Prabaharan.M

96570 views
11 replies, 1 day ago
Unstable explain plan Topic by HateOra 2 days ago

Hi,
We have observed weird behavior in the Teradata environment , Basically some of the SQLs suddenly starts running slow and when checked and compared to the previous explain plan for which it ran with optimal efficiency , there was deviation in the number of steps and even the plan was slightly different.
 
 Did anyone ever faced similar situation? 
 
Thanks

13 views
1 reply, 1 day ago
cast date between 2 different data types Topic by shaves 3 days ago #database #teradata #sql, cast, between dates

I want to add a selection criteria to my SQL.  The user will select a 'from' and 'to' date, e.g., 11/1/2015 and 12/31/2015.  The date column (jsdate) in the table is Decimal (8,0).  When I look at the dates in this column it looks like 20,150,612, 20,150,609, 20,150,611, etc.  I'm trying to add a selection critieria like "and cast(jsdate as date format 'mm/dd/yyyy' between 11/1/2015 and 12/31/2

40 views
3 replies, 2 days ago
incorrect use of subquery error Topic by p12345 3 days ago

Hi,

 

The below query is giving error while running in Teradat SQL Assistant.

 

Error- Incorrect use of subquery TGT_UPD_CNT

 

Query-

 

SELECT DERV2.LND_TBL_NM AS PERS_LND_TBL, DERV2.TGT_TBL_NM AS PERS_TGT_TBL,DERV2.TGT_INS_CNT AS PERS_TGT_CNT,DERV2.TGT_UPD_CNT AS PERS_UPD_CNT,DERV2.REJ_CNT AS PERS_REJ_CNT FROM

(

31 views
1 reply, 2 days ago
Access Right Abbreviations Topic by foxbat 20 Jul 2007

Thought this might come in handy for those of you who had trouble understanding the Access Rights abbreviations displayed in the Data Dictionary.The script below maintains the full descriptions for the access rights and can be joined to DBC.ALLRIGHTS/ DBC.ALLROLERIGHTS./************************Set up Volatile Table**************************/DROP TABLE AccessRightsAbbv;CREATE VOLATILE SET TABLE AccessRightsAbbv( Description VARCHAR(50), AccessRight CHAR(2))PRIMARY INDEX(AccessRight) ON COMMIT PRESERVE ROWS;ins AccessRightsAbbv('CHECKPOINT','CP');ins AccessRightsAbbv('CREATE AUTHORIZATION','CA');ins AccessRightsAbbv('CREATE MACRO','CM');ins AccessRightsAbbv('CREATE PROCEDURE','PC');ins AccessRightsAbbv('CREATE TABLE','CT');ins AccessRightsAbbv('CREATE TRIGGER','CG');ins AccessRightsAbbv('CREATE VIEW','CV');ins AccessRightsAbbv('DELETE','D');ins AccessRightsAbbv('DROP AUTHORIZATION','DA');ins AccessRightsAbbv('DROP FUNCTION ','DF');ins AccessRightsAbbv('DROP MACRO','DM');ins AccessRightsAbbv('DROP PROCEDURE','PD');ins AccessRightsAbbv('DROP TRIGGER','DG');ins AccessRightsAbbv('DROP VIEW','DV');ins AccessRightsAbbv('DUMP','DP');ins AccessRightsAbbv('INSERT','I');ins AccessRightsAbbv('RESTORE','RS');ins AccessRightsAbbv('SELECT','R');ins AccessRightsAbbv('UPDATE','U');ins AccessRightsAbbv('CREATE DATABASE','CD');ins AccessRightsAbbv('CREATE USER','CU');ins AccessRightsAbbv('DROP DATABASE','DD');ins AccessRightsAbbv('DROP USER','DU');ins AccessRightsAbbv('ALTER EXTERNAL PROCEDURE','AE');ins AccessRightsAbbv('ALTER FUNCTION','AF');ins AccessRightsAbbv('ALTER PROCEDURE','AP');ins AccessRightsAbbv('CREATE EXTERNAL PROCEDURE','CE');ins AccessRightsAbbv('CREATE FUNCTION','CF');ins AccessRightsAbbv('EXECUTE FUNCTION','EF');ins AccessRightsAbbv('EXECUTE PROCEDURE','PE');ins AccessRightsAbbv('EXECUTE','E');ins AccessRightsAbbv('DROP TABLE','DT');ins AccessRightsAbbv('INDEX','IX');ins AccessRightsAbbv('REFERENCES','RF');ins AccessRightsAbbv('ABORT SESSION','AS');ins AccessRightsAbbv('MONRESOURCE','MR');ins AccessRightsAbbv('MONSESSION','MS');ins AccessRightsAbbv('SETRESRATE','SR');ins AccessRightsAbbv('SETSESSRATE','SS');ins AccessRightsAbbv('CREATE PROFILE','CO');ins AccessRightsAbbv('CREATE ROLE','CR');ins AccessRightsAbbv('DROP PROFILE','DO');ins AccessRightsAbbv('DROP ROLE','DR');ins AccessRightsAbbv('REPLCONTROL','RO');/***************SQL for AccessRights held by a user***********************/SELECT UserName, DatabaseName, TableName,ColumnName, CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description ELSE ALRTS.AccessRight END AS AccessRight, GrantAuthority, GrantorName, AllnessFlag, CreatorName, CreateTimeStampFROM DBC.ALLRIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv AbbvON ALRTS.AccessRight = Abbv.AccessRight WHERE UserName='Josephm1' AND DatabaseName='E_COMPANYDB'Order By 2,3,4,5;/*************SQL for AccessRights held by a ROLE************************/SELECT RoleName, DatabaseName, TableName,ColumnName, CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description ELSE ALRTS.AccessRight END AS AccessRight, GrantorName, CreateTimeStampFROM DBC.ALLROLERIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv AbbvON ALRTS.AccessRight = Abbv.AccessRight WHERE RoleName='E_COMPANYDB_DEVELOPER' AND DatabaseName='E_COMPANYDB'Order By 2,3,4,5;

30285 views
9 replies, 2 days ago
Create Table Using SELECT Statement With Default User and Date Topic by DRossmann 2 days ago create table, Default

I'm trying to create a table, using a SELECT state, but that also has the LOAD_USER and LOAD_DATE with defaults to CURRENT_USER and CURRENT_TIMESTAMP.  If I was creating the table, then adding the data, the text for the two fields would be;

ADD LOAD_USER2 CHAR(20) CHARACTER SET LATIN CASESPECIFIC DEFAULT USER,

ADD LOAD_DATE2 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)

16 views
1 reply, 2 days ago
Select Failed 2666 Invalid date supplied Topic by shaves 3 days ago dates, error 2666, cast

SELECT
'D' || A.ZARF_ECARS_1 || A.legacy_branch_rented as Transmittal_Number,
A.ADDRESS1 as Address,
A.CITY as CITY1,
A.STATE as STATE1,
A.POSTAL as ZIP,
A.ITEM as Debtor_Reference,

cast(A.due_DT as date format 'MM/DD/YYYY')  as DueDt,

A.BAL_AMT as Amount_Due,

16 views
2 replies, 2 days ago
Block Level Compression (BLC) Topic by caitken4me 2 days ago

Working with a customer who is moving from TD13.10 to TD15 and also moving from an appliance 2650 to a 2800. The data will be moved via NPARC and all tables will land on the new system in compressed (BLC) format.

10 views
0 replies
Disable TD2 logon but allow LDAP for certain users Topic by StevenSchmid 03 Jun 2014 NULL password

Hi
We have LDAP authentication at Customer site for users, but also use TD2 connection for internal accounts such as SYSDBA.

824 views
4 replies, 2 days ago

Pages