#DateForumTypeThreadPost
14823 Dec 2009 @ 05:17 PSTAnalyticsReplyString Search and Replace.If you are looking to replace a single occurence of a string in a field, Index (or ANSI Position) statement will do it fine.For multiple occurences, you can get the oreplace UDF from Teradata.com.F...
14721 Dec 2009 @ 01:06 PSTUDAReplyNeed help loading dataJust cat the files together in Unix/ Linux.It is a simple command:cat \\IS02corp\rec\Files\Corp\IS\80720\HUB\HOTELSUMM\Logs\sales_graph_motel_level_*.csv >> allfiles.csvYou will need to speci...
14618 Dec 2009 @ 05:22 PSTDatabaseReplySpecifying a sort order while creating a tableYou cannot specify a sort order on an Insert or any other action query.So no, that will not work either!
14517 Dec 2009 @ 12:14 PSTDatabaseReplyTeradata Date manipulationThe Data Types and Literals manual has some useful info on this. Chaptewr 4 covers dates.A date is held internally as (yyyy-1900)mmdd in an integer.So 2009-12-17 is held as 1091217.To get last day ...
14416 Dec 2009 @ 11:59 PSTUDAReplyQuery NeededWith the test data, less than one second (on Express edition).If you are using real data, what is the primary index on the source table. I used a NUPI on IDcode - anything else could take a while ...
14316 Dec 2009 @ 02:37 PSTDatabaseReplySpecifying a sort order while creating a tableIt goes at the end of the sql when you run a query.You cannot specify a sort order on a table or view - only when you run against the table or view!
14216 Dec 2009 @ 08:49 PSTToolsReplyWildcard characters in MLoadNo!Easiest way is to concatenate the files in JCL (for mainframe) or with a cat command in Linux/ Unix)
14116 Dec 2009 @ 08:46 PSTUDAReplyQuery NeededCreate Volatile Table HTbl( Child_Node_Id Smallint Not Null, IDcode Char(12) Not Null, Parent_Node_ID Smallint Not Null)Primary Index (IDcode)On Commit Preserve Rows;Insert Into HTbl Valu...
14016 Dec 2009 @ 06:58 PSTToolsReplyHow is it possible to view Unicode data?Set the Session Character set to UTF8 or UTF16 before running your export. You then have to view the file in hex - I use Ultraedit but any hex viewer will do.
13915 Dec 2009 @ 04:51 PSTDatabaseReplystring contains an untranslatableOne of the columns in the query has Latin characters in one table and Unicode in the other.Check that the character sets are the same. (DBC.Columns has the character set information, or use Show Ta...
13814 Dec 2009 @ 11:21 PSTDatabaseReplyDay Name fuction in TeradataYou are right Pawan - it does not work for dates before the reference date. I have modified the code above so it is OK now.
13714 Dec 2009 @ 07:58 PSTDatabaseReplyDay Name fuction in TeradataYou can use date arithmetic:Select CASE ((Date '2009-12-14' - Date '1900-01-01') Mod 7) + 1WHEN 0 THEN 'Sun'WHEN 1 THEN 'Mon'WHEN 2 THEN 'Tue'WHEN 3 THEN 'Wed'WHEN 4 THEN 'Thu'WHEN 5 THEN 'Fri'WHE...
13613 Dec 2009 @ 09:00 PSTDatabaseReplyConverting a field value to a column nameSee http://www.teradata.com/teradataforum/Topic17517-5-1.aspx#bm17547
13511 Dec 2009 @ 10:09 PSTAnalyticsReplyTransposing Data1. Set up test data---------------------------------------------------Create Multiset Volatile Table "Input Data"("Account Type" Char(12) Not Null, "Date" Date Format 'mm/dd/yyyy' Not Null, "Value"...
13409 Dec 2009 @ 12:16 PSTAnalyticsReplyTransposing DataYou need to run the following script in bteq.It creates table Pivot with the required columns and populates it.:-.Foldline 1,2,3,4,5,6,7,8,9,10.Suppress on 1.Export Report File = Runsql.txt-- Gener...
13309 Dec 2009 @ 08:41 PSTAnalyticsReplyTransposing DataTeradata (and ANSI SQL) do not directly support pivot/ crosstab queries.This is a presentation level function, so is handled by your presentation tool.If you just get the Account Type/ Date/ Sum(Va...
13207 Dec 2009 @ 02:19 PSTDatabaseReplyLooping in teradataYou do not need a loop.SelectId, FName, LnameFrom TblHaving Count(*) > 1Group By 1,2,3;
13106 Dec 2009 @ 03:36 PSTUDAReplyRemoving duplicatesNo.Teradata does not have a similar concept to Oracles' Rowid. You will have to do an Insert/ Select Distinct.
13003 Dec 2009 @ 04:02 PSTDatabaseReplyMacros - Create tableYou can do a Create Table as long as it is the last statement in the macro.Not really sure why you want to do it as a macro though - why not just create the 25 tables (one for each month) in a bteq...
12902 Dec 2009 @ 08:16 PSTDatabaseReplyEquivalent for db2 Chr function to handle line feed and tabset V_TEXT = oreplace (V_TEXT, 'abc;', '0a'XC) ;set V_TEXT = oreplace (V_TEXT, 'def9;', '09'XC);XC is hexadecimal value as a character
12827 Nov 2009 @ 05:36 PSTToolsReplyTearadata Query and Result using Export UtilityIf it is a bteq batch script, why not just redirect the output to a file and edit out the "Query Completed" and any other messages you do not want.
12727 Nov 2009 @ 05:32 PSTToolsReplyHow to Skip or Get first and Last Record from Flat File through MultiLoad and TPUMP Utility? If they are a header and trailer, you should have a record type so that you can process them.The other option is to use a sed script to remove them before passing the file to the utility.
12627 Nov 2009 @ 02:21 PSTDatabaseReplyComment string is longer than permittedMaximum length is 255 characters - can be Unicode or Latin.To see the comment, do a Help Database dbname - it appears here.SQLA explorer window does not show it.
12526 Nov 2009 @ 05:21 PSTUDAReplyStored Procedure or SqlDefinitely SQL. Only use a stored procedure when there is some form of looping required and the code within the loop is based on changes in previous records.If you are going to do this in a proc, y...
12424 Nov 2009 @ 01:01 PSTUDAReplyHow to use Coalesce to replace 'zero'SEL Coalesce(NullifZero(sum(a.TotScanAmt)),1) TotScanAmtFROMT1 aYou may not want to use CASE, but Coalesce and Nullifzero are both forms of CASE!

Pages