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 handle Null values in FASTload Topic by aneelkumar04 26 Jul 2016
Define
 colum (CHAR(19), nullif='                   '),
 colum2 (CHAR(35), nullif='                                   '),
 colum3 (CHAR(2), nullif='  '),
 colum4 (CHAR(4), nullif='    '),
 colum5 (CHAR(19), nullif='                   '),
 colum6 (CHAR(4), nullif='    '),
247 views
4 replies, 3 years ago
The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT Topic by shekar.sr 28 Jul 2016 Merge error on PPI

I am trying to use Push down optimization on teradata database.
while informatica is creating a merge sql, getting the error (The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT).

207 views
0 replies
Set unix variable inside bteq Topic by ysrinu 16 Feb 2012 bteq, label, unix variable

Hi,

i have a script where i want to set unix variable inside bteq script and then use this variable

further in the bteq script too.

script1.sh

VAR_FLAG=""

bteq << EOF >>${LOG_FILE} 2>&1

SELECT BT_FLAG from db1.table1;

<set unix variable VAR_FLAG with BT_FLAG from above>

16864 views
4 replies, 3 years ago
Find the list of active running queries through SQL Topic by gskaushik 24 Jul 2015 Teradata SQL ACTIVE QUERIES

Hi,
 
I am using Teradata 13.10, I want to know the list of queries which is executing in Teradata using sql.
Thanks for your help

2210 views
11 replies, 3 years ago
ROW_NUMBER - TIE-BREAKER Topic by AtardecerR0j0 28 Jul 2016 row_number, TIE-BREAKER

How row_number function gets tie-breaker when there are the same value for diferent rows of the order by list?

115 views
0 replies
Cursor select * with Fetch Next statement Topic by ac255034 28 Jul 2016 cursor, fetch, into, teradata, sqlserver

I am working on FETCH STATEMENT inside CURSOR. I need to convert a SQL server script into Teradata.
-----------------------------------------------------------------------------------------------------------------
Following scnerio is working in Teradata( SELECT columns )

SQL SERVER input:---------------------------------------

DECLARE vend_cursor CURSOR  

292 views
0 replies
COUNT () OVER (PARTITION BY) and DISTINCT Topic by gwenaellebarzic 20 Aug 2013

Hello !
I create this topic, because I have a question concerning the COUNT(My field) OVER (PARTITION BY My Other Field) and the DISTINCT statement.
Here is the situation : I have in one table the following lines :
Name;Hair Colour;Timestamp
Ted;brown;t1
Ted;red;t2
Ted;brown;t3
Ted;blue;t4
Ben;blond;t5
Ben;green;t6
Ben;purple;t7

48546 views
10 replies, 3 years ago
How to Write the correct syntax for Join Topic by rahul1987 27 Jun 2016 inner join

q) Use COUNT and DISTINCT to determine how many distinct skus there are in the skuinfo, skstinfo, and trnsact tables. Which skus are common to all tables, or unique to specific tables?
A) I was trying to find the solution to the above q in TERA DATA. The first part was simple i was able to run three commands and got the distinct skus (stock keeping unit).

627 views
8 replies, 3 years ago
104 views
1 reply, 3 years ago
Help Combing Two Queries Topic by sandi99 26 Jul 2016

Hi there - very novice at SQL.  Looking to combine two queries and not sure how to go about. I am trying to add company_name to Query 1.  company_name is in Query 2 which uses two entirely different tables.  Of course, I am at a loss here.  Appreciate any assistance!  Thanks in advance!
Query 1 uses: 

147 views
2 replies, 3 years ago
Convert string into date Topic by dr.murthy_td 06 Jun 2013

Hi ,
 
Could you  please suggest me how to convert the string value '4/6/2013' to date,
string value coming in different formats
4/6/2013
12/6/2013
6/12/2013

55983 views
14 replies, 3 years ago
TPT ODBC Topic by john9 26 Jul 2016

Hi All  -
I have been struggling to fix my ODBC TPT script.Here is the back ground:
My teredata version is 14.10
I am trying to extract the data from AS400 (accessed through DB2 client) using ODBC TPT script.
Below is the error message i am getting:
 
Teradata Parallel Transporter Version 14.10.00.02

146 views
3 replies, 3 years ago
Need to get View Column derivation info from teradata Topic by NewbieTD 27 Jul 2016

Hi All,
I want to get all view columns derviation/definition information. Is there any way to get them.
eg:
my table name is - DB.ORG_Table
which has 3 columns namely col1,col2,col3
i have created view (Myview) as "repalce view DBV.Myview as locking table DB.ORG_Table access select col1 as id,(col2/col3)*100 as Cal_Val from DB.ORG_Table".

110 views
3 replies, 3 years ago
What is difference between Redistribution of rows and Duplication of small table in AMPs Topic by kamlesh114 26 Jul 2016

Related to join strategy used by TD optimzer.
 
When we join table and see explain plain then  it show that rows are redistributed/duplicated on all AMPs based on join startegy used by Optimizer to get the final result.
Please correct me..
1.Redistribution - Rows from one or both table are redistributed to all the AMPs based on hash code.

157 views
3 replies, 3 years ago
Need ER Modeling for Below Requirement Topic by abhijeetgupta29nov 27 Jul 2016

You want to design a database to manage information on the collection of various types of taxes (stamp duty, real estate, automotive patent, gross income) by a state agency. Taxpayers are some entities that are taxed and may be individuals or companies.

 

63 views
0 replies
OLAP Function for Recurring Deduction Topic by lr_shp 26 Jul 2016 cumulative sum, OLAP functions, iterative deductions, partitioning

Hi All, 
I'm trying to come up with an OLAP function solution to a units deduction problem for various groups. 

132 views
0 replies
Different Column Orders for Stats Recommended by DIAGNOSTIC HELPSTATS? Topic by ricejfx 26 Jul 2016 stats

Hello,
I am running into a spool space problem on tables that I just created, which had no statistics.  I employed the DIAGNOSTIC HELPSTATS ON FOR SESSION in order to get the recommended stats (admittedly, this was just so I didn't have to type them all).  When I started to grab that stats that I thought would be helpful, I noticed the following recommendations:

122 views
1 reply, 3 years ago
Fastload Topic by abhishank18 25 Jul 2016

Is there any way to access the Error Tables in case there is an error while fastload process, As selecting from error table throws an error saying table is being loaded ? If not how can we know due to which row the load has paused ?

 

178 views
8 replies, 3 years ago
Help in XML shredding Topic by SteveTheBlue 17 Mar 2015

Hi, apologies if this is the wrong forum.
I am new to XML shredding and wondered if anybody could advise why the call on the procedure at the end of the following fails saying "CALL Failed.  [9134] AS_SHRED_BATCH:Error in function ASSHRED_GETTABLES: Invalid annotated schema. "
Thanks for any advice.
 
 

-- Create a file with the XML document

1431 views
3 replies, 3 years ago
How to get column data type and format Topic by venkata_k01 26 Jul 2016

I am working on one automation process where data will be fetched from a table based on qualifying key passed as parameter. That qualifying key can be of type date/timestamp. I need to handle all formats of date in the where statement and if the type is timestamp,no need to convert timestamp to any format, just use that column in the where statement.

94 views
0 replies
Fastload Duplicate Records issues Topic by aneelkumar04 19 Jul 2016

Hi All,
 
I am loading data from file to Db using fastload. My file got aborted due to duplciate records,but when i chcecked in file there is no duplicate records in file. I am confusinng. How its possible.
 
Log :
Total Records read   = 2987
Total Error Table 1  = 0 Table has been dropped
Total Error Tabel2 = 0 Table has been dropped

232 views
7 replies, 3 years ago
Help needed for understanding of space default behavior and limitations or rules Topic by agts 25 Jul 2016

Existing Database

 

Database Name = A1

Perm Space    = 100 GB

Spool Space   = 100 GB

Temp Space    = 100 GB

 

 

CREATE DATABASE A11 FROM A1

AS PERMANENT  = 20 GB;

 

What will be default Spool and Temp space allocated to database A11 ?

 

CREATE DATABASE A12 FROM A1

73 views
2 replies, 3 years ago
TPT Script Error While executing Topic by MohanMuchivolu 25 Jul 2016

Hi,
I am getting below TPT error while reading from MQ 
MQ_READER[1]: TPT19434 pmOpen failed. General failure (34): 'Teradata Websphere MQ AMOD/PIDMMain(!ERROR!).  --ERROR: Ignoring unnecessary pathname specification at open'
MQ_READER[1]: TPT19304 Fatal error opening file.
MQ_READER[1]: TPT19015 TPT Exit code set to 12.

89 views
0 replies
CASE statement vs UDF Topic by raka 25 Jul 2016 case stament

Hi,
I'm a java developer and new to TD and looking for some input/advice.
We have a query where we are using "CASE". After rough profilling found this "CASE" part is taking more time compared to others. The query is working fine for smaller data sets but it is CPU timing out of larger datasets . How do I get around with the CPU timeout ?
 

70 views
0 replies
UDF Default Database Topic by andydoorey 29 Mar 2016 udf

Is there a database where we can create user defined functions so that it is not necessary to qualify them with the databasename when we use them.  The UDFs that have been created by the Teradata installation are all in the TD_SYSFNLIB database, and don't need to be prefixed with the databasename.  What I'm after is a place to put our own functions so we can use them in a similar way.

339 views
3 replies, 3 years ago

Pages