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.
date variable not parsing proerly in Stored Procedure Topic by HanC 14 Jul 2016 dynamic sql; parameters; stored procedures;

Hi, can anyone please help? I'm expecting to get the same date '2016-02-01' displaying with below code, instead I got number 2013. so, it's doing the math: 2013 = 2016-2-1
By the way, why I'm doing the test code below? because another more complicated code didn't work and it turned out to be the date variable making trouble. 
 

195 views
2 replies, 3 years ago
how to make Stored Procedure with dynamic SQL to display the records? Topic by HanC 14 Jul 2016 stored procedure, dynamic sql; parameters; stored procedures;

Can anyone please help? Below code can finish running with no error, but how to make it to display the records, just so I know I'm getting what I want. Thanks!

 

replace PROCEDURE dlcna_cateam.sp_test3 

(

IN SRC_DB_NM VARCHAR(30)

, IN SRC_TBL_NM VARCHAR(30)

)

DYNAMIC RESULT SETS 1

BEGIN

DECLARE QUERY1 VARCHAR(4000);

299 views
3 replies, 3 years ago
Viewing Generated Dynamic SQL Query Topic by Hart3945 14 Jul 2016 dynamic sql; parameters; stored procedures;

We recently converted many of our static stored procedures to dynamic sql to reduce the amount of duplicated code.  Does Teradata have a function or process that would allow the users to view the SQL statements that would be generated by passing in different parameters?  I've heard SQL Server has a similar function that would allow you to call the stored procedure and display the SQL statement

106 views
1 reply, 3 years ago
Teradata DBA documents Topic by drmkd17 14 Jul 2016 dba

Hi People,
I need some Teradata DBA docuemnts that would gear me up for a Teradata DBA Role. Can anybody help me out here.
Thansk,
Katie

122 views
0 replies
TD 14 - Data Storage Questions Topic by sallad 10 Jul 2016 char, varchar, create table, INSERT INTO
--create table 
CREATE SET TABLE database.table,
 NO FALLBACK ,
 NO BEFORE JOURNAL,
 NO AFTER JOURNAL,
 CHECKSUM = DEFAULT,
 DEFAULT MERGEBLOCKRATIO
 (
        some columns...,
 unique_number_10_digits CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
 )
PRIMARY INDEX( unique_number_10_digits );

--inserts
247 views
4 replies, 3 years ago
NOT IN and nested query Topic by AROLD001 13 Jul 2016

Hi Developers,
I have consistently used NOT IN on hard-coded static list of values in Teradat SQL Assistant and it has worked without any issues.
 
Sel distinct col1
from table1
where col1 NOT IN ('ABC', 'XYZ');
 

217 views
4 replies, 3 years ago
Formatting CURRENT_TIMESTAMP Topic by aarsh.dave 23 Jan 2014 CURRENT_TIMESTAMP, format

Hi All,
I want to select the current_timestamp in a specific format 'YYYYMMDDHH24MISS'.
Can you please let me know how I should go about it?

4831 views
7 replies, 3 years ago
Advice on ELT process please Topic by crispo3 13 Jul 2016 oracle migration, elt, etl

Hi,
We're transitioning from Oracle to Teradata, and we need to replicate our existing ELT process and i'm after some advice/opinions about available options. 
We have a file -> stage process already in place, using TPT LOAD

279 views
1 reply, 3 years ago
Avoid all-row scan in driving table using NUSI Topic by RATTLESKIN 13 Jul 2016 all-row scan, SecondaryIndex

Hi I am gettnig all-row scan even after using a secondary Index at the driving table. I am not sure if my approach is right , so please help me out here.

 

148 views
3 replies, 3 years ago
Need to generate a row number like column Topic by Vga 13 Jul 2016 rank, row number

As per below data, I need to generate a rank or row number type column based upon the following scenarios:

 

1) For a Location-Order-ID combination, it should be a same number irrespective of other columns (like Class/Date in this example).

138 views
3 replies, 3 years ago
ALTER_PPI Topic by samapika.t 17 Feb 2014

While going through Partition primary index I saw to add and drop the range partitions.
My question is
Is not it possible to modify the PPI created by case partition?????
If yes kindly give the syntax

1540 views
10 replies, 3 years ago
Regarding Teradata Migration Topic by Koushik Chandra 13 Jul 2016 database migration

We are planning to do a Teradata migration, the versions for source and target Tearadata is close (source Teradata version 15.00.02.02, though not sure about Target version yet). The migration is happening because of company intake by other company.

My question is what are points in general, we have to consider (always keep in mind), for the migration to be successful.

118 views
1 reply, 3 years ago
3899 Internal error in teradata SQL parser Topic by sreeram Praneeth 03 Dec 2015

Hi ,
 
Please help me know how the below issue can be resolved.
 
Im trying to equate a timestamfield(casting to date formate ) with current_date in the where clause. and im getting 3899 Internal error in teradata SQL parser error.
 

301 views
4 replies, 3 years ago
extending MLPPI tables Topic by domingo.maynard@gmail.com 25 Dec 2013

I am having trouble extending partitions for the table below, the table is populated and I was able to extend range for level1. however i get error on extending level2, I was able to extend level2 range on an empty table. Can someone help me?
 
ALTER TABLE COD.SOME_TABLE_S_C12126                   
MODIFY PRIMARY INDEX (mstr_cust_ky)

1958 views
8 replies, 3 years ago
Any dynamic lookup concept in teradata? Topic by quest 07 Jul 2016 scd2, dynamic lookup, avoid duplicates

Hi all,
Was wondering if there is anything like dynamic lookup concept where duplicates from source can be prevented from loading into target table?
Source table
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col | col9 | col10
Intermediate table (For SCD-2 identifying columns are col1, col2, col4)

161 views
1 reply, 3 years ago
Audit on Teradata Topic by cvsanthosh 12 Jul 2016 audit

Hi,
Need to capture users who access sensitive information in teradata database. Able to get all the required information from dbc.accesslog such as userid, date and time of login, databasename, tablename and columnname accessed. Need to add additional information as part of audit report such as

141 views
0 replies
High Skewfactor inspite of even distribution Topic by drmkd17 24 Jun 2016 Skewfactor

Hi All,
 
Today i came across a weird scenario in Teradata. 
One of my tables dbc.table_a  was highly skewed with a skewfactor of 95.77 . I used the below query to check the skewfactor.
SELECT
TABLENAME,
SUM(CURRENTPERM) /(1024*1024) AS CURRENTPERM,
(100 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)*100)) AS SKEWFACTOR
FROM
DBC.TABLESIZE

194 views
6 replies, 3 years ago
JDBC queries on Teradata not logged in DBQLogTbl with parameters. Topic by Wonder 18 May 2015

Hi,
We have an application that connects to Teradata via JDBC and executes queries using PreparedStatements.
However, when logging is enabled for the application user, the QuertText in DBQLogTbl doesn't show the actual query with values of variables(see below)
select col1 from tab2 where col3=? and col4=?

1258 views
4 replies, 3 years ago
Find the minimum alignment date query based on alignment number Topic by johnsunnydew 10 Jul 2016

Hi Frinds,
Need your help.
I need to find the minimum alignment date of alignment numbers.

Alignment No

Alignment_Date

30

20161007

31

20161008

32

20161009

40

20161008

41

20161009

42

20161010

56

20161101

73 views
1 reply, 3 years ago
Teradata SQL Assistant 15.00 DROP STATISTICS not working Topic by PeterSchwennesen 06 Oct 2014 SQL Assistant 15.00 DROP STATISTICS

We cannot drop statistics with the syntax we normally use, when using the new SQL Assistant 15.00.
We have tried logging on both with ODBC, with several changes in the setup and with Teradata logon.
I tested with:

2778 views
10 replies, 3 years ago
Storing History DAta into tables Topic by drmkd17 11 Jul 2016 History data, huge table, performance

I have a requirement to fit in 441GB of history data into a table which makes my table bulky and difficult to query. Just to explain it is a fact table and stores amount fields etc. I have a partition on the business date field.
What will be the best strategy to handle this requirement with respect to performance. . Experts can you please suggest.

209 views
7 replies, 3 years ago
Faster way to delete and rebuild a table Topic by thnkgreen 11 Jul 2016 #Fastload

Hello everyone.  This is my first post.  I have a question about deleting and rebuilding tables.  I have inherited some code that is structured like
1.  create an empty table in the sandbox  (I'll call it table A)
2.  insert all data from an existing table (I'll call it table B) to table A
3.  drop table B

188 views
4 replies, 3 years ago
Restore using DSA with NO BUILD option. Topic by RonaldBrayan 08 Jul 2016

Hi,
I have a large restore around 5 TB, I'm using DSA to Backup and restore. I want to restore with NO BUILD option and BUILD the tables separately using ARCMAIN(Build Data Table (xx.yy) ...) . I do not find no build option in DSA. Can anybody help? 
I could do the same thing in TARA by unchecking BUILD option and build separately.
 

141 views
2 replies, 3 years ago
saving a python DataFrame to Table Topic by rompstar 11 Jul 2016

hi guys // so I am learning python, because that is a tool more and more requested these days at companies of all size (small and large).
So, I am able to Connect into the database fine using PYODBC.  What I am trying to do is this:
I have a Excel file that is saved to a DataFrame in python and I want to insert that information into a Table, we want this to be automated

139 views
0 replies
SQL error using Teradata JDBC Topic by tdreturn 13 Jun 2016

Has anybody run into this where the JDBC driver acts inconsistenly. We understand the inconsistency but there does not seem to be a pattern to the inconsistency. All we are trying to find out is the inconsistent pattern. We are TD 14.10 and using the 15.00 driver. Shown the query below.

288 views
4 replies, 3 years ago

Pages