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 add a new spatial reference system to SYSSPATIAL.SPATIAL_REF_SYS Topic by franceszhu 10 Aug 2016 geospatial

I want to add a new SRS record to the table SYSSPATIAL.SPATIAL_REF_SYS. However, the manual doesn't have more description on it. There are 4 columns in the table:

SRID: Spatial reference system identifier.

AUTH_NAME: Name of the standard or standards body that is being cited for this reference system.

142 views
1 reply, 3 years ago
Capturing Versions when there is a Change in a Column Value else keep the Earliest if Consecutive Topic by sam141988 10 Aug 2016

Hi Experts Can you All please suggest how this can be achived in TD-
Consider the Sample Source Data -
ID|START_DATE|VALUE
110333005|2000-05-03 00:00:00.000000|0.00
110333005|2014-01-08 12:38:40.000000|0.00
110333005|2014-01-10 20:26:58.000000|103.14
110333005|2014-01-22 07:50:20.000000|0.00
110333005|2014-01-28 23:19:42.000000|0.00

137 views
4 replies, 3 years ago
Lock a table row by rowhash Topic by dipakrana 09 Aug 2016 rowhash lock missing record

Hello,
   Is it possible to explecitely lock a row or a set of rows of a table in a bteq/tpump through out the session?  

77 views
1 reply, 3 years ago
TimeZone conversion Topic by srikanth471 10 Aug 2016 timezone change

Hi All,
 
I have a scenario here as below
 
TAB1 has 100Bn rows with timestamp(RPTG_TS) in 'America Pacific' and need to convert this timestamp column to GMT timestamp.
For now i have timezone shift table( Src_TZ,Tgt_TZ,from_dt,to_dt,shift), TAB1 left join Timezone on (rptg_ts between from_dt and to_dt and Src_Tz='America Pacific'

106 views
0 replies
Options to compare contents of two tables Topic by blue_eyed_byte 09 Aug 2016

Hello,
From your experience, what is the best option for comparing contents of two tables? Both ways!
- full outer join?
- minus applied both ways?
- except?
- not exist applied both ways?
Let's say that we have 2 tables: extract and extract_previous and every day you have to check the diff between them. 
 
thanks in advance!

105 views
2 replies, 3 years ago
Convert text to a timestamp Topic by yarekshy85 13 Jun 2016 cast, timestamp

Hi All,
I am trying to replicate the below vertica conversion to timestamp to teradata, But I am getting error. Can someone please guide me?
Vertica:
to_timestamp('June 7, 2016, Hour 0','Month DD, YYYY, Hour HH') 

--output:

'2016-06-07 00:00:00'

 

 

 

 
 

245 views
3 replies, 3 years ago
How to call a function from a procedure call in Teradata Topic by ljohnny 10 Aug 2016

I am creating a stored procedure. Let's call it SP1. In SP1, I need to call SP2, and one of the input parameter needs to be passed in by calling a function Func1
****************************************************************
REPLACE PROCEDURE SP1(
IN Var VARCHAR(20), IN Var2 VARCHAR(20), IN Var3 VARCHAR(20)
)
BEGIN
    DECLARE FuncResult VARCHAR(255);

66 views
0 replies
convert varchar data into timestamp Topic by krishna1985 09 Aug 2016

Hi All,
can you please help me with this
 
I am trying to convert below varchar records into timestamp.
 

 2016-08-03 8:32:03

2016-08-03 11:56:34

 

Tried multiple ways of casting but not ablt to get.

 

Regards,

Krishna

76 views
1 reply, 3 years ago
Merge the continuous rows with date range( sequential rows should merge) Topic by crazyrama5 09 Aug 2016 dnoeth;Raja_KT;fred;feinholz

Hi All,

 

Can somebody please help me with following scenario. I am looking for dynamic solution of SQL. Because number of rows are not static.

CUST, OLD_ST_DT, OLD_END_DT, AMT1, AMT2 columns are kind of keys, (CUST, OLD_ST_DT, OLD_END_DT, AMT1, AMT2) should not be sequential duplicate when we see the with  NEW_ST_DT, NEW_END_DT columns.

 

100 views
2 replies, 3 years ago
retrieve row count from Teradata Macro Topic by tejakandikonda86 09 Aug 2016

Hi,
 
I have a Macro 'ABC' which would take one input parameter. I am executing the macro as execute ABC(?) through jdbc.
This Macro returns results with column values. However I need to  get the number of rows this Macro returns.
 
Is there something like select count(*) from Macro we can use?

102 views
1 reply, 3 years ago
Retrieve Rows where a change in values (intwo columns) occured Topic by mk104 04 Aug 2016 teradata

Hi all,I will try to explain this in the best way possible.
I have a dataset and I am trying to retrieve the only the rows where a change has occurred. I have tried QUALIFY but I can't get my head around it.
For example, looking at the following,

171 views
3 replies, 3 years ago
DBC - CURRENTPERM EXCEEDS MAXPERM Topic by BRIANH 30 Jul 2016 BRIANH

I had a power outage and of course Teradata was running. 
Teradata - 13.1
I'm running Terdata under VM player and there apparently are no issues with Teradata starting and stopping...

140 views
2 replies, 3 years ago
How to do a random sample? Topic by Yunfei Zhao 08 Aug 2016

I want to do a simple random sample in Teradata. I have a table, My_Table, it has 2 columns, SSN and Tran_ID. This is for a bank, like Citibank, which has millions of customers (SSN), and each customer has hundres of transactions (Tran_ID). I want to do a simple random sample of 10% of customers (SSN). And get all the transactions for these random 10% customers.

181 views
1 reply, 3 years ago
User's DDL Topic by Cesco 05 Aug 2016

Hello everybody,
Do you know if there is a way to know the DDL of an existing user?
In addition, I need to know if a user has the logon privilege or not, but I can't find how can do it.
 
Thank you, in advance.

242 views
5 replies, 3 years ago
Numeric overflow occurred during computation in Teradata Stored procedure Topic by Priti_Kanal 09 Aug 2016

INSERT INTO TGT
SELECT 
DECIMAL_C1,
DECIMAL_C2,
MAX(CAST(DATE_C3 AS DATE)) AS MAX_DATE_C3,
MAX(CAST(TIMETSAMP_C4 AS TIMESTAMP(6))) AS MAX_TIMETSAMP_C4
(
    SELECT 
    DECIMAL_C1,
    DECIMAL_C2,
    DATE_C3,
    TIMETSAMP_C4 
    FROM TAB_1
    WHERE DATE_C3 < DATE '2016-08-09' 
 UNION 
    SELECT 
    DECIMAL_C1,
    DECIMAL_C2,
    DATE_C3,
    TIMETSAMP_C4 
126 views
0 replies
Looking to count the first event in a colum Topic by Jonathan.S 08 Aug 2016

Hi,
I am trying to identify a repeat caller as deliniated by group code I have a data set as that has a colum of event id's (that can potentialy be duplicates) a colum of interaction id's that are unique, a date time field. and a subscription ID

83 views
0 replies
Looking for keywords in a string of text in a variable from another table Topic by davidlarge22 08 Aug 2016

hi all,
 
i have ran out of ideas for this one 
 
i have a table where there are 4 results that have keywords in them so 
 
Word 
a
b
c
d
and i have another table where these key words may be in that variable like 
A        B        C                D 
john Daivs london i went to shop in A 
 

55 views
0 replies
Need recursive SQL code Topic by rdeshpande 12 Jul 2016

Hi,
 I have data in following format:
I have first two columns  and I need to get third column using SQL/Recursive SQL.
The first two columns show mobile number and their call time to call center. I want to flag each call whether it is a repetitive call or not.

128 views
3 replies, 3 years ago
My data mover job sometimes work and sometimes hang Topic by nick2408 06 Feb 2016

Hi
My requirement is to move a complete databases, along with the tables and data from one server to another. I have a DM job that I am using which normally does the task but most of the times it doesnt do anything. I see that the sessions in the target server, are always in idle state (when seen from viewpoint) Any pointers to what might be causing this?
Below is my xml

218 views
1 reply, 3 years ago
Move data from one Teradata DB(Prod Server)to another Teradata DB (dev Server) Topic by kartaj 22 Apr 2016 data mover

Hi Team,

i have a requirement, that i need to move data from one teradata DB to another Teradata DB, Difficutly is I need to move around 40 tables daily, is there any process that it can be done automatically, so that it can reduce manual effort.

 

189 views
1 reply, 3 years ago
Cannot view internal dbs control settings through viewpoint -teradata 1650 - Error Illegal option Topic by Arslannadeem 04 Aug 2016 #DBS #EDA #Extreme Data Appliance

Hi,
 
I am trying to view DBS internal settings through remote console on teradata 1650(Extreme Data Appliance) .
 
It gives an error "Illegal option".
 
Can anyone help please.
 
Regards,
Arslan

86 views
1 reply, 3 years ago
Consolidate rows Topic by HF 19 Jul 2016 recursive

I'm looking to consilidate rows in my table by combining similar consecutive rows together. 
In the example I have category "A" where the type "X" is the 1st two rows in order of START_TS. I need those two rows to combine with the start_TS from the 1st row and the END_TS of the second row. The other two rows would remain the same.

209 views
5 replies, 3 years ago
Teredata Error 5497: CALL cannot be submitted in multi-statement request Topic by WAQ 05 Aug 2016

Hi,
I have a macro in which I have two statements. One is the SP call and the other one is a SELECT statement. The body of both SP and macro is given below:

158 views
2 replies, 3 years ago
UNABLE TO CONNECT TERADATA STORED PROCEDURE FROM TABLEAU Topic by larun1616 05 Aug 2016 stored procedures, tableau, connectivity issue

I would need to pass value in the form of a Tableau Parameter to a Teradata Stored Procedure. On doing this I am getting an error "The stored procedure "[DATABASE].[PROCEDURE_NAME]" does not return a result set, or the result set columns do not have names.

194 views
0 replies
How do i write qualify rank() over (partition.....question Topic by prawen 20 Sep 2012 informatica, rank()

Hi all..
 
i have the query

qualify rank() over (partition.....question

 

in teradata form.  now i need to convert it to infomatica. can anyone help me provide the conversion for the "rank()" in informatica.???

 

thanks in advance..!!

 

this query is almost similar to previously asked but need answer in different form.

17507 views
7 replies, 3 years ago

Pages