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.
Day Of Week or Week-Ending Function? Topic by myokitis 22 Mar 2010 date, dates, week, function

I need to rollup and report data by week, with the week being defined as Sun through Saturday and identified as the Saturday-date. Does Teradata SQL have any functions that could facilitate this?

For example, if there was a"WEEKDAY" function as in Access or Excel, I could derive it with date math, but I couldn't find anything like that in my documentation.

Thanks.

46206 views
9 replies, 4 years ago
Online backup performance Topic by goldminer 15 Feb 2014

Generally speaking, can anyone tell me if online backups perform better than regular backups?  In other words, if I am backing up a 500gb table, which method would back up quicker, online or regular?
Thanks,
 
Joe

1028 views
5 replies, 4 years ago
TACTICAL QUERIES ON WINDOWS FUNCTION Topic by pietro.nardella 04 Jul 2016 tactical queries, WINDOWS FUNCTION

Dear ATRs,
I have a "simple" view V made by a "driving" table T (250 Mln rows) and a "left table" L (220 Mln rows) with a window function row_number inside:
 
REPLACE VIEW V  AS  LOCK ROW FOR ACCESS
SELECT
(CURRENT_DATE -1 )   AS     DAT_RIF,
  COD_ABI_CEDENTE  ,

94 views
0 replies
Manually generate create table Topic by Noa_Shahak 03 Jul 2016 columnar, manually generate ddl

Hi All,

 

I'm looking for a way to manually generate create table (as another table) script. 

The output should be script because I need to edit it.

 

My final goal is to create table (regular table without columnar) as columnar table.

 

Can anyone please assist?

 

Thanks,

Noa.

 

151 views
2 replies, 4 years ago
How to call unix commands with in the BTEQ script? Topic by tdradha 07 May 2012

Hi ,

 

Could anyone help in how can I write unix commands with in the Bteq script?I am failing and the below is the error msg I am getting:

sh: -c : line 1 : syntax error : unexpected end of file

here is what I am trying to do....Much appreciated if you have solution for this..

vi abcd.sh

if [[ cond1 ]]

then 

if [[ cond2 ]]

10302 views
3 replies, 4 years ago
Calling a stored procedure from Teradata Rest API Topic by SyedShah 03 Jul 2016 Rest_API

Hi All,

I am trying to call a stored procedure from Teradata Rest API and i am unable to do so.

77 views
0 replies
How to find Table name, column name, column datatype and Number of row in table Topic by teja420 30 Jun 2016 ColumnName, table name, column datatype

Hi Team,
Can any one please help me with the query to pull all Table name, column name, column datatype and Number of row in table in teradata.
Please respond. Thanks in advance
Regards,

124 views
2 replies, 4 years ago
Error when trying to create a function Topic by jpw111082 30 Jun 2016 udf, timestamp, unix timestamp

Hi, I am trying to create my first function in Teradata SQL Assistant using a script I found on the internet.  the purpose of the function is to convert a timestamp to a unix timestamp and is as follows;
 
CREATE OR REPLACE FUNCTION oracle_to_unix(in_date IN DATE) RETURN NUMBER 
IS 
BEGIN 

239 views
1 reply, 4 years ago
Revoke User Topic by wambli 09 Feb 2012

I have a script that revokes logon on firecall users after X days.  It will then drop the user 14 days after that.  I am looking for the table that contains the flag/field that shows the user was revoked.  I want to use this as a check to do a compare. 

6702 views
4 replies, 4 years ago
Analytical Function for deriving dates Topic by prem.rpk 29 Jun 2016 database, sql, OLAP functions

Hi Everyone,
                    I am new to Teradata forum. Could anyone in forum please help out in deriving an analytical function.
Trying to create a new date column using windowing function which display date of child (SEQ_ORDER (5)) as is and move dates of its first parent (SEQ_ORDER (4)) to all the parent level i.e 3,2,1
 

Table:

163 views
1 reply, 4 years ago
Syntax Error: WHERE "a"."Field1" IN("b"."Test1", "b"."Test2") Topic by KellnerK 29 Jun 2016

I have a query that cross joins a table of information against a table of dynamic dates that I create on the fly.
For some reason, when using IN, I cannot list multiple fields.  It will run and not error if I only provide one list in the where clause:

WHERE

("a"."SITE_ID" IN ("b"."Var1"))

 

98 views
3 replies, 4 years ago
Shapefile doesn't load the GEOM part for 1 out of 4 rows? Topic by teradatatester 29 Jun 2016 shapefile, shape file, st_geometry, GEOM

I have a shapefile that has 4 territories. Three of the territories have a territory name and one is null because it is the row for everything that is outside of the 3 territories.
The shape file loads without an error message but the third row doesn't load the GEOM part for some reason. The fourth row is the one which is null and the GEOM for that row loads fine.

115 views
0 replies
OREPLACE issues between environments Topic by loafob 29 Jun 2016 oreplace, udf, function, 7509

Hello,
 
I have a query using OREPLACE that runs in one environment, but fails in another with:
 

Executed as Single statement.  Failed [7509 : HY000] Result Exceeded maximum length for UDF/XSP/UDM pm_edw_etl_load.oreplace2. 
Elapsed time = 00:00:00.437 
 
STATEMENT 1: SEL  failed. 

 

187 views
1 reply, 4 years ago
Difference between NOT IN() AN NOT(COL1 IN)) Topic by boagus1 28 Jun 2016 NOT IN(), NOT(COL1 IN())

A developer on our project is using logic that I can't find in any Teradata documentation, so I was hoping for some insight on it's performance.
NOT(ORDER_ID IN (2,3,4) is how it's coded.
ORDER_ID NOT IN (2,3,4) is how I've always seen this logic coded.
Oddly enough when testing, ORDER_ID NOT IN (2,3,4), I found records with ORDER_ID'S 2,3 AND 4.
Any ideas?
 

102 views
1 reply, 4 years ago
Transaction control in BTEQ script Topic by quest 28 Jun 2016 bteq, Transaction control, scd2

Hi all,
I am coding for SCD (Slowly changing Dimension) 2 in BTEQ script. What should be ideal code to avoid mess up of data considering SCD-2?
As of now, if there is an error we quit but is there any optimal solution?

152 views
1 reply, 4 years ago
COLUMNAR TABLE- SELECT TOP N* FROM COLUMNAR TABLE NOT GIVING OUTPUT Topic by pushkarcse 21 Jun 2016 Columnar table

Hi,
I am facing one issue with the columnar table. I am unable to fetch records using TOP N* command from a columnar table with 256 columns.

SELECT TOP 10*

FROM COLUMNAR TABLE

--Returns no rows

 

But when I am applying filter condition, I am able to fethch records using TOP N* command,

 

SELECT TOP 10*

81 views
4 replies, 4 years ago
Recommendations for dynamic phrase searching with tagging output. Topic by TDScott 28 Jun 2016 cursor, freetext, search

Hi All,

Looking for recommendations on Full Text search Tagging via LIKE / LIKE ANY etc.

 

This has to be dynamic, from a table of terms and tags.

 

I have a billion row table of free text (myPK bigint + myTEXT varchar 4000).

I have a 200-500 row table of phrases and/or words tied back to Tags. (myTAG varchar(10) + myPHRASE varchar(100))

128 views
2 replies, 4 years ago
leading zeroes Topic by YIAPIPI 28 Jun 2016

Hi all,
i trying to get in a 3 digit "INT" field, the leading zeroes. 
substantially i need to change "1" to "001" or "12" to "012" and so on.....
 
executing the query ....
SELECT
substr('000' + cast(ID_FD AS CHAR(3)),3) as ID_FD_modificato, ID_FDFROM DXDWH_IRION_VW.DWH_FD
i get something wrong
ID_FD_modificato               ID_FD

129 views
2 replies, 4 years ago
it seems teradata doesn't support != as not equal, right? Topic by charon 09 Feb 2012

Hi all,

       I have try the command Column_name != Value in where clause, but teradata alerts error [3707: 42000] expected something like an 'IN' keyword or a 'CONTAINS' keyword between the word 'RETAIL_PRICE' and '|'.

17812 views
5 replies, 4 years ago
Transaction QUERY_BAND not working Topic by mm185159 23 Jun 2016

Whenever I set a Transaction Query_Band, the only statement recorded in QryLog table is the SET QUERY_BAND= statement. The following SQL transaction does have the Transaction QueryBand Name/Value pair associated with it.
By Example:
SET QUERY_BAND='STEP=1.0;' FOR TRANSACTION
;insert into DBA_SANDBOX.EPIC21_gt_svc_agmt_sample_list
...
From Query Log Table:

96 views
3 replies, 4 years ago
special char 0xFF convert to 0X1A with character set LATIN1_0A Topic by blankme 24 Jun 2016 character set, LATIN1_0A

i encountered a character set problem make me headache, please someone can help me.
there is a table t_special with database sever character set 'LATIN', then i execute the sql insert into t_special (specialchar) values('FF'xc) with session character set 'LATIN1_0A'. (column specialchar is varchar(10) type).

188 views
2 replies, 4 years ago
Maximum row length exceeded in recursive query Topic by svdata 27 Jun 2016 Failure 2805, Recursive query

    

99 views
0 replies
What is the equivalent of "CONNECT_BY_ISCYCLE" in Teradata Topic by ChiaraReply 27 Jun 2016 CONNECT_BY_ISCYCLE, oracle

Hi 

 

I must make a porting of a query in Oracle that it  contains the CONNECT_BY.

 

 

I know that in Teradata it is replaced with the WITH RECOURSIVE, but there is also  the function  CONNECT_BY_ISCYCLE.

 

How can I replace it in TERADATA?

 

 

121 views
0 replies
Any benefit in NUSI on "customer_type" column? Topic by dnsmkl 26 Jun 2016 performance nusi

Hi,
Is there any point/benefit in creating secondary index on column,
which has low number of distinct values compared to size of the table.
Hypothetical example:

67 views
1 reply, 4 years ago
Packdisk Performance Query Topic by SG255017 26 Jun 2016

Hi All
Is it possible to run packdisk on multiple tables at a time? If yes could you please share the steps in Ferret.(fyi...I know the steps for single table packdisk)
Please also let me know whether any performance impact due to this activity.
Thanks
Sanket 

50 views
0 replies

Pages