#DateForumTypeThreadPost
7108 Sep 2016 @ 07:19 PDTDatabaseReplyCollapse one to many into one SQL query result row create multiset volatile table Task_Table ( Task_ID integer, Resource_ID integer )primary index( Task_ID ) on commit preserve rows; insert into Task_Table va...
7007 Sep 2016 @ 12:55 PDTDatabaseReplyFind skew with perm over 1gb 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 (CURREN...
6902 Sep 2016 @ 07:45 PDTDatabaseReplyALTER STATEMENT - IF THE TABLE HAVE MORE THAN ONE BILLION RECORDSYou can have a look to the DNOETH's blog. It's very interesting http://developer.teradata.com/blog/dnoeth/2013/08/why-dont-you-use-alter-table-to-alter-a-table
6812 Aug 2016 @ 03:29 PDTToolsReplyAny way to find the original query that created a table? select requestText from dbc.tables where tablename='tablename' and databasename='databasename' ; Keep in mind that on this way if you modified a table with "Alter tabl...
6711 Aug 2016 @ 06:18 PDTDatabaseReplyCan we find out Column Level Mismatches between 2 Tables in Terdata?To do that you should use full outer join way so you can join using key columns and compare the atribute columns one by one
6611 Aug 2016 @ 02:51 PDTTeradata ApplicationsReplyFast load failure with return code 12look up on the fastload documentation for: DISPLAY_ERRORS and NOSTOP
6511 Aug 2016 @ 02:38 PDTDatabaseReplyCapturing MAX marks for Each Student when the MArks are in Columns select greatest(9,35,2)
6411 Aug 2016 @ 02:32 PDTDatabaseReplyCapturing Versions when there is a Change in a Column Value else keep the Earliest if ConsecutiveVery important: Read the documentation to understand Row_Number and min functions http://www.info.teradata.com/htmlpubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/ROW_NUMBER.html A...
6311 Aug 2016 @ 12:31 PDTDatabaseReplyLock a table row by rowhashYes it is possible, here you have an example: BT; LOCKING ROW FOR WRITE SELECT NULL FROM TABLE1 WHERE COL1 = 'XXXX'; .... ET; If one session has 1 rowhash blocked and another one do ...
6211 Aug 2016 @ 12:03 PDTDatabaseReplyCapturing Versions when there is a Change in a Column Value else keep the Earliest if Consecutive drop table table1; create multiset volatile table table1 ( id integer, start_date timestamp, valor decimal (5,2) )primary index( id ) on commit preserve rows; insert into table1 values(...
6128 Jul 2016 @ 08:24 PDTDatabaseTopicROW_NUMBER - TIE-BREAKERHow row_number function gets tie-breaker when there are the same value for diferent rows of the order by list?
6020 Jul 2016 @ 12:08 PDTDatabaseReplyFastload Duplicate Records issuesYou can load data again using a NOPI table, so that you'll load duplicate rows. Then you can use sql to check about duplicate rows.
5919 Jul 2016 @ 03:50 PDTDatabaseReplyUsing Volatile table in stored procedureThere is no problem using volatile tables in a store procedure  I used on a SP where after executing the SP i should review a volatile table, this table sometimes existed previous to the exec...
5815 Jul 2016 @ 12:40 PDTDatabaseReplyViewing Generated Dynamic SQL QueryYou can use DYNAMIC RESULT SETS . Then you have to create a cursor using then string variable where you storage the dynamic sql statement, for instance: DECLARE cursor_1 CURSOR WITH RETURN...
5713 Jul 2016 @ 06:50 PDTDatabaseReplyNeed recursive SQL code select mobile, calltime, max(calltime) over (partition by mobile order by calltime asc rows between 1 preceding and 1 preceding) calltime_previous, case wh...
5613 Jul 2016 @ 06:15 PDTDatabaseReplyAny dynamic lookup concept in teradata? --For SCD-2 identifying columns are col1, col2, col4 select normalize col1, col2, col4, period( start_dt, end_dt ) from table1;  
5512 Jul 2016 @ 06:58 PDTDatabaseReplyFind the minimum alignment date query based on alignment number drop table Example; create multiset volatile table Example ( Alignment_No integer, Alignment_Date date )primary index( Alignment_No) on commit preserve rows; insert into Example(30, cas...
5428 Jun 2016 @ 06:04 PDTDatabaseReplyleading zeroes select 7 (format '999')(char(3)) This is other way
5327 Jun 2016 @ 04:46 PDTDatabaseReplyAny benefit in NUSI on "customer_type" column?That NUSI doesn't look selective enough. Nevertheless you can create it, collect statistics on the index, and then do an explain referencing the NUSI. If the parser chooses a FTS over using the...
5222 Jun 2016 @ 06:24 PDTGeneralReplyRetrieve columns having a specific valueThe columntype filter depends on data type, I was supposing the data type was char or varchar
5122 Jun 2016 @ 06:23 PDTGeneralReplyRetrieve columns having a specific valueYou can write the filter condition using a dynamic SQL like this: select trim(columnname)||'=''ABC'' or ' from dbc.columns where databasename='YourDabaBase' ...
5022 Jun 2016 @ 04:08 PDTDatabaseReplyIdentifying which column is throwing error on INSERT select * from YOUR_TABLE where to_number(trim(COL_N),'9999999999') is null With this one you can discover about wrong data. Repead this operation with all the columns or include the ...
4915 Jun 2016 @ 09:11 PDTDatabaseReplycreate mutiple tables from an existing table with while loopI tried the process before sending and It works
4815 Jun 2016 @ 02:59 PDTDatabaseReplycreate mutiple tables from an existing table with while loopYou can use a dynamic SQL like this one: select 'create table tab'||(day_of_calendar (format '999')(CHAR(3)))||' as (select * from mydata where seq='||day_of_calendar||&#...
4701 Jun 2016 @ 03:25 PDTDatabaseReplyTable counts in a databaseThis is not a procedure or macro. This is a dynamic SQL so you should execute the answer of this query. Firt of all you shoul replace the string '<your_db>' and write yours. ...

Pages