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 ?
 

153 views
2 replies, 1 year 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;

103 views
6 replies, 1 year 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

47 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

28 views
1 reply, 1 year 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 ?
 

41 views
3 replies, 1 year ago
Find blocking sessions and tables Topic by teradatauser2 06 Sep 2016 blocking queries

Hi,

63 views
4 replies, 1 year 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

41 views
2 replies, 1 year 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:

 

80 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.

60 views
3 replies, 1 year 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

24 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,

13 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%';

25 views
1 reply, 1 year 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 

25 views
1 reply, 1 year 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

(

24 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'

57 views
2 replies, 1 year 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

34 views
1 reply, 1 year 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:

20 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?

120 views
6 replies, 1 year 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:
--

52 views
2 replies, 1 year 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.

61141 views
17 replies, 1 year 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.

109 views
4 replies, 1 year 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;

60 views
2 replies, 1 year 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:

66 views
3 replies, 1 year 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.
 

69 views
1 reply, 1 year 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. 

 

62 views
2 replies, 1 year ago

Pages