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.
Rules for Multi value compression Topic by Deepakji 08 Sep 2016

Can anyone help me on the below questions..
 
1. Is MVC allowed on primary index ?
2. Having MVC on too many columns will create problem ?
3.Is appliying compression on truncate and load tables are not ideal ?
4.Can we apply MVC on derived columns ?
 

47 views
2 replies, 7 months ago
Can we pass object as parameter in a macro ? Topic by Deepakji 23 Aug 2016

For eg : 

replace macro database1.macro1(databasename char(50),tablename char(50))

as

(

Select count(*) from :databasename.:tablename;

63 views
6 replies, 7 months ago
TO_NUMBER returns wrong value Topic by rob_watson 08 Sep 2016 to_number

Hi,
I'm trying to convert a series of hex values (stored as strings) to numbers ( BIGINT) using the TO_NUMBER function . A number of the results giivng the incorrect number
SELECT TO_NUMBER( '03ea02653f6938ba','XXXXXXXXXXXXXXXX' ) returns 282040560542890190
According to my scientific calculator the value is 282040560542890170

12 views
0 replies
Collapse one to many into one SQL query result row Topic by 808healthcare 07 Sep 2016

I would like to collapse records from a one to many relationship down into a single query result row, a task can have one or two resources assigned (there is no limit on the number of resources that can be assigned but for this exercise let’s say the number can never be zero or more than two)
 
Task Table
Task_ID                  Resource_ID

12 views
1 reply, 7 months ago
Collect Stats with insert statement Topic by Aganediwal 07 Sep 2016

Hi all,
I am using below two statements to create table and insert some data from an existing table.
Is there a way in which stats will be automatically collected after insert statement completes or a third statement of manually collecting stats is the only way ?
Can something be included with Insert statement to collect the stats ?
 

19 views
3 replies, 7 months ago
Find blocking sessions and tables Topic by teradatauser2 06 Sep 2016 blocking queries

Hi,

38 views
4 replies, 7 months ago
Compute with immediate previous row value derived Topic by deven_rk 07 Sep 2016

Hi All,

 

I have the below data, wherein I want to compute 'Amount' data dynamically in a 'Select' statement as given below, here I want

to refer the previous row value, apply an equation, compute a new value store it, for the next row again the same process of referring the

20 views
2 replies, 7 months ago
volatile tables inside the stored procedure Topic by SGIT 07 Sep 2016 stored procedure, volatile table

Hello, 

 

I have the following two questions please:

 

26 views
0 replies
Teradata block storage Topic by pandit 03 Sep 2016

Hi All,
This is my first post :).  So pardon me in case of any mistake.

44 views
3 replies, 7 months ago
Compute with immediate previous row value derived Topic by deven_rk 07 Sep 2016

Hi All,

 

I have the below data, wherein I want to compute 'Amount' data dynamically in a 'Select' statement as given below, here I want

to refer the previous row value, apply an equation, compute a new value store it, for the next row again the same process of referring the

5 views
0 replies
Drop Table blocked by Read lock session which is not on the machine. Topic by nari1309 07 Sep 2016

Hi All,

I having an issues, while Dropping a backup table,

6 views
0 replies
System Table for Compression Topic by agts 07 Sep 2016

Is there any way for finding compressed columns in database with there values, like system table for compression columns and values? 
using
Select * from dbc.columns where compressible like 'C%';

16 views
1 reply, 7 months ago
Find skew with perm over 1gb Topic by vc 06 Sep 2016

Hello,
I am using the below query 

SELECT 

cast(DatabaseName as char(30)),

cast(Tablename as char(40)),

(100 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)*100) (FORMAT 'zz9.99') )AS Skewfactor_greater_90,

cast (CURRENT_date as timestamp(0)) + ((current_time -time '00:00:00') hour to second(0)) As AS_ON

FROM 

DBC.TABLESIZEV 

15 views
1 reply, 7 months ago
Syntax Topic by SGIT 06 Sep 2016

I am getting the following error : SPL1027:E(L7), Missing/Invalid SQL statement'E(3706):Syntax error: expected something between the word 'StartDate' and the 'DATE' keyword.'. Looks like a syntax error where i am declaring the variable StartDate .

Please advice. Thanks.

 

 

CREATE MULTISET VOLATILE TABLE TEMP2

AS

(

8 views
0 replies
Creating a Dynamic YoY Date Range in Case Statement for Current Quarter QTD Topic by kthumm11 01 Sep 2016

Let's say I want to find YoY QTD Q3 unique customers and today is 9/1/2016.  How do I make it so I do not need to manually need to update the bolded date ranges in the case when statement.
If today were 10/5/2016 the date ranges would automatically change to '2016-10-01' and '2016-10-04' then 'Q4_2016' '2015-10-01' and '2015-10-04' then 'Q4_2015'

46 views
2 replies, 7 months ago
OLAP SQL - running count/total Topic by deven_rk 05 Sep 2016

Hi All,

 

We have the below data and using an SQL not procedural code, I would like to get the output as given below for 'out_count', 

it would help if we could do it using OLAP functions with Preceding and Following keywords, thank you.

 

cal_date, holiday_flag, out_count

'01-Aug-2016', 0,0

'02-Aug-2016', 0,0

21 views
1 reply, 7 months ago
How to return the result of a select query in a function Topic by juanalfonso 06 Sep 2016 #STOREDPROCEDURE

Hello to everybody,

I'm trying to return the result of a select query in a function. Something like:

12 views
0 replies
Merge into operation Topic by Soumyatg 22 Jul 2016

I am rewriting an insert statement which was taking lot of time, as a Merge statement. The target table is Multiset also it has a composite NUPI. the source may have non unique rows. Is it at all possible to achieve this by using Merge statement?

107 views
6 replies, 7 months ago
Grouping while combining Date and Hour Topic by mrjkirkpatrick 02 Sep 2016

Hello,
I couldn't find exactly this question among the great date/time posts on the forum.
I have a date field (row_added_ts) with the format: "12/29/2014 6:34:42 PM"
My end goal is to group 'cases'  by the hour, but with the date included (so a simple HOUR EXTRACT seemingly won't work).
e.g:
--

28 views
2 replies, 7 months ago
Sql to delete duplicates in Teradata Topic by Naveen_K 07 Aug 2009

Hi ,

Is there a single SQL statement to delete duplicate records from a table in Teradata.
Note , I don't want to use any intermediate table , like inserting into an intermediate set table.

Thanks in Advance.

60995 views
17 replies, 7 months ago
DBC Diskspace, Topic by Dinesh1975 01 Sep 2016

We have load jobs running in the night. DBC diskspace getting filled rapidly. I can see it in the view point, it raises from 11% to 80 %. or some time it reaches 100 % and all the jobs get failed. My question is, IS DBC Diskspace filling is normal ?. is there a way to reduce it to keep the jobs running.

54 views
4 replies, 7 months ago
Insert taking long Topic by krishna1985 31 Aug 2016

Hi Gurus,
 
I have the below script everything works well but at the bottom of the script we have insert in to statement ie for UDRBSCMS.RCD_TPB_OVERALL_DECISION and its taking ages to load. I am not sure whats wrong here. We jsut have 60000 records.
 
DROP TABLE RCD_TPB_OVERALL_DECISION;

44 views
2 replies, 7 months ago
I can't execute an SP that calls another SP inside Topic by juanalfonso 01 Sep 2016 #STOREDPROCEDURE

Hello,

I'm trying to execute a procedure (F_SEPARA_LETRAS_Y_NUMEROS) with another procedure called inside (F_QUITA_REPES) but I get the following error:

43 views
3 replies, 8 months ago
ALTER STATEMENT - IF THE TABLE HAVE MORE THAN ONE BILLION RECORDS Topic by srinivas_teradata 02 Sep 2016 alter, ddl, modify, alter table

Hi,
I am Altering the table for addition of new columns. And tables is having more than 1 billion records.
Please let us know is it standard to alter the table with more than one billion records or not. Also guide us in best practise to alter the table with data.
 

24 views
1 reply, 8 months ago
replacing existing column Topic by SGIT 01 Sep 2016

Hello,

 

In the below query, i get the error 

 

CREATE TABLE Failed. 3515:  Duplication of column PRCD_AMT in a table, derived table, view, macro or trigger. 

 

37 views
2 replies, 8 months ago

Pages