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 identify "down" tables in all Teradata system Topic by jn512032 1 hour ago

I have a problem to find "down" tables in entire Teradata system. Does it exist somethink like a "SELECT tablename FROM dbc.some_table WHERE something_like_down_attribute? Thank you for the advice
 
Jiri

11 views
0 replies
Error from Insert Select statement Topic by donal28 12 Jun 2012

Hi All

I am getting an error when running the following Insert Select statment

Statement

INSERT INTO  DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO (BORROWER_NAME)

 

select  DISTINCT TRIM (CM_CUST_NAME)

FROM DDHWV03P.CM_CUSTOMER_STATIC_PERIODIC

4397 views
6 replies, 3 hours ago
Overlapping data handling in SQL Topic by tejas.dalvi@gmail.com 6 hours ago dates overlap

Hi All,

My requirement is as below.

 

I am having data as below in one of my table 

key1 key2 amt start_dt end_dt

1      11     10  2015-01-01 2015-01-14

1      11     10  2015-01-15 2015-01-31

1      11     10  2015-02-01 2015-02-15

1      11     15 2015-02-16  2015-02-25

1      11     10 2015-02-26 2015-03-15

31 views
0 replies
One string out of the repetitive string series Topic by nagendratrpth 6 hours ago Expression

Hi,
How to get only one word out of the repetitive word series for exanple XYZ FROM XYZ XYZ XYZ or XYZ,XYZ,XYZ or XYZ?XYZ?XYZ.
Thanks
Nagendra

37 views
0 replies
How to bring un-utilized space for utilization in terdata Topic by prasanth225 4 days ago

Hi,
I am going through some of the topics on space utilization and got confused. Can you please clarify the below points:
1. I have calculated
SUM(CurrentPerm)/1024/1024/1024 &
MAX(CurrentPerm) * (HASHAMP()+1)
 
for a particular database in DBC.DISKSPACE. The outputs are 1084 and 1.25 respectively.
 

259 views
6 replies, 9 hours ago
VT table data getting deleted after "Multiple rows updated" error Topic by nagendratrpth 1 day ago volatile tables

Hi,
what could be the reasson for data deletion after  "Multiple rows updated" error automatically ?
Thanks
Nagendra

93 views
1 reply, 17 hours ago
Outlier Detection Topic by siran1 22 hours ago oulier detection

Hello,
I was hoping to get a little help on some sql. I have a table that contains account number which are created in a sequential order, thus there is a dependancy between date it is created and the account number. Some of these accounts have incorrect creation date, which i need to identify and correct. ex.
Date    Acct
7/1      108
7/1      100

95 views
1 reply, 19 hours ago
SET table with PPI - optimizer plan Topic by syam1406 1 day ago ppi, SET table, performance

Hi,
We have a set table with NUPI and is partitioned with date. My question is when we try inserting a new record based on row hash it will perform duplicate checking or it will go to particular partition and perform duplicate check?
Ex: in one AMP
Primary Index(ex: ID1): 10
Partition 1 with date 2015-01-01: 100 rows
partition 2 with date 2015-01-02: 200 rows

140 views
3 replies, 22 hours ago
Export/Import data through Teradata SQL Topic by nagendratrpth 1 day ago data import

Hi,
Do we have any feature in teradata where we can export/import data through a Teradata SQL query .( Not SQLA exprot/import utility).
Lets take a scenerio : we are connected TD from SAS , created one temp table and want to import data from local machine excel/txt file to that temp table and then using that temp table as a dataset in SAS.
Thanks
Nagendra

47 views
0 replies
Error: Invalid operation for DateTime or Interval Topic by Sean_m 2 days ago

Pretty new to Teradata and I've been stuck on this error for a little bit.
Trying to fill an empty table with data from a differenet table. Everything is the exact same between the tables, except for the partition. 
 

INSERT INTO Measurement_Fact_1

SELECT * FROM Measurement_Fact;

 

181 views
4 replies, 1 day ago
Using MERGE into a table with an identity column. Result: Failure 5758 Topic by boxerdog 24 Sep 2012 identity, merge, 5758

 
Hello all,
I would like to point out what I think is a bug and solicit ideas for a way to make this work.
I have a stage table and I want to merge it into a table with a surrogate key.  I want auto-generate the surrogate key on insert and I only want to update records that need updating.

6512 views
5 replies, 1 day ago
Row_number(0 function not working as expected Topic by archana_m161 1 day ago

Hi
I am using row number function in my select and s=inserting the resultset into some other table. But it is not working as expectedand am missing soem records in new table.
Here is my select sql:
select clr_id, id,station , c_date,permfeed,clr_status_key,1
from (

42 views
0 replies
Script to compare two databases (Tables and Views definitions) Topic by M.Saeed Khurram 06 Dec 2013 Database comparison

All,
I have to compare two databases (Tables and Views defintions), Both the databases have the same number of tables and views, but I have to investigate the difference in there definitions, for example SET or MULTISET TABLES.
If anyone have scripts to make this comparison, Please let me know!
 

1340 views
6 replies, 1 day ago
Optimizing Stats Collection Topic by Kishore_1 4 days ago Optimizing Stats Collection

Hi,

 

I have a huge table.

I want to collect stats on a few important date fields in the table, which are used by business for reporting purpose.

Even collecting full stats on these columns is quite resource intensive and taking 5-6 hours.

198 views
3 replies, 1 day ago
Inserting Unicode Characters into a Unicode Field Topic by JoeL 312 03 Mar 2015 insert, unicode, utf8, 6706

Teradata 13.10 is rejecting my insert with the error:
 The string contains an untranslatable character. (6706)
Here is my INSERT statement:
Insert Into CCDW_T.UTest (ucol, i) SELECT 'ɂ',578;
This is my CREATE Table:

616 views
3 replies, 1 day ago
Teradata Stored Procedure GOTO Topic by Santanu84 2 days ago #STOREDPROCEDURE, error in stored procedure call

Hi
I am using TD 14.0 and writing a stored procedure where using GOTO I am trying to move to another Label.
But surprisingly it is showing me error as below.
 
SPL1027:E(L34), Missing/Invalid SQL statement'E(3703):'GOTO' is a Teradata SQL keyword reserved for future use.'.

94 views
1 reply, 1 day ago
Generating Rank value for each SET of flags Topic by maheshkanni 4 days ago @dnoeth, @Fred, @Raja_KT, @Adeel Chaudhry
Hi All,

I need a data like below, I want to generate a rank value for each set of Flags. 

seq market  product startdt  enddt  flag
1 10 100 5/10/2015 5/16/2015 N
2 10 100 5/17/2015 5/23/2015 N
3 10 100 5/24/2015 5/30/2015 Y
4 10 100 5/31/2015 6/6/2015 Y
5 10 100 6/7/2015 6/13/2015 Y
6 10 100 6/14/2015 6/20/2015 N
7 10 100 6/21/2015 6/27/2015 Y

206 views
4 replies, 2 days ago
Find position of first non-digit or space from the back Topic by eric_td 09 Jan 2015

Hi,
I want to separate fields like example

 

Field_source         O/p field1      O/p field2

abc def 234          abc def           234

xyzyzy                   xyzyzy 

24 hours 5656        24 hours          5656

High7stars 675       High7stars        675   

 

 

 

422 views
7 replies, 2 days ago
Replace NULL Values in Table with Most Recent NOT NULL values Topic by Indyan 3 days ago last_value, teradata, sql, update

I have a table with the following layout

cust_acct_id (PK)| effect_dt | expiry_dt | curr_rec_ind | field_a | field_b | field_c | field_d

I have to populate any NULL values in field_a, field_b, field_c, or field_d with the most recent value received for that field for the same cust_acct_id.

Sample Input:

324 views
5 replies, 2 days ago
How to get total IO for a system Topic by gggggg 06 Mar 2015 System IO utilization

Hi,

474 views
1 reply, 2 days ago
How to use dervied table in another derived table using with cluase in SQL? Topic by NewbieTD 3 days ago
TD:
with tmp as (select 1 as a,2 as b),
tmp2 as (Select * from tmp)
select * from tmp2;

Output: select failed, object tmp doesn't exist.

Db2:

with tmp as (select 1 as a,2 as b from sysibm.sysdummy1)
,tmp2 as (Select * from tmp)
select * from tmp2

Output:
A B
1 2

Hi All,

233 views
4 replies, 2 days ago
Global Tempoary Table Error Topic by joe.mingmoang 16 Feb 2010 volitile global temp table transient journal

Hello Everyone,

I have tried to create a GLOBAL TEMPORARY table, but unfortunately with no success. The error message i get is ;

3524: The user does not have CREATE TABLE access to database MYUSERID.

Can anyone help?

Many thanks

2496 views
5 replies, 3 days ago
Curious case with optimiser explain Topic by Karam 3 days ago

select   a.column1

 

            a.column2

 

            a.column3

 

            ....

 

            a.column20

 

from     table a

 

 

 

 

 

Table a is a big table , with count (*) around 800 million. 

 

 

 

91 views
0 replies
Problem with history table Topic by chava70 4 days ago

Hi, I am trying to build history table which contains all the changes for a event. The columns(column_s, column_o, column_m , column_r) are from 4 different tables which describe the events for a particluar key. The columns(Key and date) are common in all the tables.

296 views
6 replies, 4 days ago
How to delete records from self referencing table Topic by Arohi 4 days ago

Hi ,
 
I have a  table with self reference . how to empty the table???
Referencing column is a primary key.
 
Please suggest any solution.
 
Regards,
Arohi

139 views
1 reply, 4 days ago

Pages