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 ?
|
632 views
|
2 replies,
5 years 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;
|
531 views
|
6 replies,
5 years 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
|
277 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
|
413 views
|
1 reply,
5 years 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 ?
|
282 views
|
3 replies,
5 years ago
|
|
262 views
|
4 replies,
5 years 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
|
265 views
|
2 replies,
5 years 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:
|
312 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.
|
216 views
|
3 replies,
5 years 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
|
223 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,
|
259 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%';
|
210 views
|
1 reply,
5 years 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
|
192 views
|
1 reply,
5 years 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
(
|
199 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'
|
227 views
|
2 replies,
5 years 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
|
228 views
|
1 reply,
5 years 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:
|
205 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?
|
346 views
|
6 replies,
5 years 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:
--
|
278 views
|
2 replies,
5 years 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.
|
62400 views
|
17 replies,
5 years 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.
|
347 views
|
4 replies,
5 years 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;
|
251 views
|
2 replies,
5 years 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:
|
552 views
|
3 replies,
5 years 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.
|
373 views
|
1 reply,
5 years 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.
|
275 views
|
2 replies,
5 years ago
|
Pages
|
|