#DateForumTypeThreadPost
22302 Dec 2010 @ 12:52 PSTDatabaseReplyConvert string into DateSelect Cast(('01.12.2010' (Date,Format 'dd.mm.yyyy')) As Date) ;
22201 Dec 2010 @ 09:08 PSTDatabaseReplyCreate table as..with data...with compression?I ran the following on TD12; it has been around for a while though. Create Table CT1 (PK Smallint Not Null , VC Varchar(12) , SI Smallint , CF Char(3) ) Unique Primary Index (PK) ; Ins...
22130 Nov 2010 @ 04:46 PSTDatabaseReplyError while Executing FASTLOAD ScriptThe default format for a bteq export is report format. You cannot have varchar output on a report - it is converted to char. The bteq export then becomes: .set width 2000; .set titledashes off;...
22030 Nov 2010 @ 11:05 PSTDatabaseReplyconverting record values to column namesSee http://forums.teradata.com/forum/analytics/transposing-data
21928 Nov 2010 @ 02:21 PSTDatabaseReplyNeed help handling null and empty valuesYour handling for the character fields will work, but it is over-complicated. You will be far more popular with: 1. CASE WHEN MyField = '' OR MyField is Null THEN 'Undefined' ELSE MyField End...
21815 Nov 2010 @ 01:10 PSTToolsReplyRetain Previous record field valueAnother method commonly used is to pre-process the file using sed/ awk on Unix/ Linux (Sort on mainframe) to take the employer-id from the M record and add it to the following B records.
21711 Nov 2010 @ 10:32 PSTToolsReplyTPT Load Operator error table being loaded?If you specify locking for access on rhe query, you can usually read the table: Locking errortable for access Select * From errortable;
21609 Nov 2010 @ 06:49 PSTAnalyticsReplyDynamic viewsDepends how and when you want the date to change! If the dates are always last month - based on calendar dates, the following will work: SELECT Table1.* FROM TABLE1 INNER JOIN TABLE2 ON TAB...
21509 Nov 2010 @ 06:27 PSTDatabaseReplyDeconcatenation of column into multiple columnsSP is stored procedure. It allows looping in SQL, so it allows you to code (in this case) an endless loop taking a value and looking for a sbsequent value form that point. (There are other features...
21409 Nov 2010 @ 04:20 PSTDatabaseReplyDeconcatenation of column into multiple columnsThe following will break down your string into a maximum of 4 fields. If you want to go to an infinite length, you need to go to an SP to allow for looping. Create Volatile Table T1 (PK Smalli...
21302 Nov 2010 @ 11:05 PDTDatabaseReplyLPAD Function equivalent in TeradataApologies; copied over the wrong query! Select Substring('0000' From 1 For 4-Chars(Trim(Seq_No)))||Trim(Seq_No) From Tbl Order By 1; Works fine.
21201 Nov 2010 @ 03:49 PDTDatabaseReplyLPAD Function equivalent in TeradataSQL assistant uses Microsoft to format its datatypes. Assuming your seq-no is already a character datatype: Select Substring('0000' From 1 For Chars(Trim(Seq_No)))||Trim(Seq_No) From tbl; (...
21101 Nov 2010 @ 03:45 PDTDatabaseReplyNumeric overflow....2616Select Count(*) (Float) From tbl; It can count as high as the table can grow!
21013 Oct 2010 @ 06:59 PDTDatabaseReplyCreating a Timestamp(6) with Timezone column with Default valueYou need to tell dbc that the character constant is really a timestamp. create table t1( record_end_dtm TIMESTAMP(6) with TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ ' NOT NULL DEFAULT Timesta...
20911 Oct 2010 @ 03:07 PDTDatabaseReplyHelp on decimal display formatIt depends on what you are using to present your data. Cognos or BO will do this type of thing directly. If you are using Queryman or Bteq, you will have to format the string as a character to ...
20811 Oct 2010 @ 02:29 PDTDatabaseReplyHow the below statement will execute? Please explain?SELECT ';INSERT INTO x_data_hist.hist_x ( Seg_ID, Group_ID, Individual_ID, Agreement_ID, Sel_Ind, Ctl_Ind, Mail_Ind, Rsp_Ind, Target_Ind, Dup_Ind ) SELECT S.Seg_ID, S.Group_ID, S.Ind...
20701 Oct 2010 @ 03:02 PDTDatabaseReplyHow the below statement will execute? Please explain?For every TBL_NM in work_x_table (with a Seg_Typ_Id other than 15 or 17), it will generate an insert statement to copy the appropriate records into the hist table. These statements are presumably ...
20601 Oct 2010 @ 02:55 PDTDatabaseReplyCollecting Stats after deleting all dataIt could be a historical thing. In older Teradata releases, you could Collect Statistics on a table (without specifying columns or indices) and it would recollect all defined stats in a single pas...
20516 Sep 2010 @ 08:19 PDTDatabaseReplyCurrentPerm different after restoreIt is very common. Restore essentially does a row (not block) level restore - because the system you are restoring to may not have the same hardware config or hashmap. So when it restores, you get...
20403 Sep 2010 @ 06:46 PDTDatabaseReplyCan a macro take a list as an argument?Or you can: exec myMacro( ('''chicken'',''pig'',''cow''') );
20315 Aug 2010 @ 12:49 PDTDatabaseReplyFastExport output file formatIf you have a varchar, you get a length field for the string. Here, where you concatenated a number of strings, you just ge a length field for the line, not individual strings. So cast the line a...
20229 Jul 2010 @ 10:57 PDTDatabaseReplyAdd a column in a create table as(sel...) statementSomething like: create table temp as (SELECT A.*, 'Cust NAME' AS Field_Nm, Trim(C.FRST_NM)||' '||Trim(C.LST_NM) As Field_Txt FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN c ON B.P_ID =...
20119 Jul 2010 @ 05:12 PDTToolsReplyfload and mload on empty tableFastload does not use as any work or log tables, just the error tables. Mload has a log table and work tables to create, so there is more setup time - even if the file is empty.
20013 Jul 2010 @ 11:54 PDTToolsReplyfastload insertion I agree with Steve. You can use Rob's Insert in Multiload, or put the "raw" columns from the file in a work table and calculate the derived columns on the move to the target table. Alternatively,...
19912 Jul 2010 @ 10:02 PDTDatabaseReplyPlease help me with the sum functionSelect Product_Id , Total_Sales , D1.AllSales From tbl Cross Join (Select Sum(Total_Sales) As AllSales From tbl) D1

Pages