148 | 23 Dec 2009 @ 05:17 PST | Analytics | Reply | String 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... |
147 | 21 Dec 2009 @ 01:06 PST | UDA | Reply | Need help loading data | Just 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... |
146 | 18 Dec 2009 @ 05:22 PST | Database | Reply | Specifying a sort order while creating a table | You cannot specify a sort order on an Insert or any other action query.So no, that will not work either! |
145 | 17 Dec 2009 @ 12:14 PST | Database | Reply | Teradata Date manipulation | The 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 ... |
144 | 16 Dec 2009 @ 11:59 PST | UDA | Reply | Query Needed | With 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 ... |
143 | 16 Dec 2009 @ 02:37 PST | Database | Reply | Specifying a sort order while creating a table | It 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! |
142 | 16 Dec 2009 @ 08:49 PST | Tools | Reply | Wildcard characters in MLoad | No!Easiest way is to concatenate the files in JCL (for mainframe) or with a cat command in Linux/ Unix) |
141 | 16 Dec 2009 @ 08:46 PST | UDA | Reply | Query Needed | Create 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... |
140 | 16 Dec 2009 @ 06:58 PST | Tools | Reply | How 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.
|
139 | 15 Dec 2009 @ 04:51 PST | Database | Reply | string contains an untranslatable | One 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... |
138 | 14 Dec 2009 @ 11:21 PST | Database | Reply | Day Name fuction in Teradata | You are right Pawan - it does not work for dates before the reference date. I have modified the code above so it is OK now. |
137 | 14 Dec 2009 @ 07:58 PST | Database | Reply | Day Name fuction in Teradata | You 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... |
136 | 13 Dec 2009 @ 09:00 PST | Database | Reply | Converting a field value to a column name | See http://www.teradata.com/teradataforum/Topic17517-5-1.aspx#bm17547 |
135 | 11 Dec 2009 @ 10:09 PST | Analytics | Reply | Transposing Data | 1. 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"... |
134 | 09 Dec 2009 @ 12:16 PST | Analytics | Reply | Transposing Data | You 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... |
133 | 09 Dec 2009 @ 08:41 PST | Analytics | Reply | Transposing Data | Teradata (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... |
132 | 07 Dec 2009 @ 02:19 PST | Database | Reply | Looping in teradata | You do not need a loop.SelectId, FName, LnameFrom TblHaving Count(*) > 1Group By 1,2,3; |
131 | 06 Dec 2009 @ 03:36 PST | UDA | Reply | Removing duplicates | No.Teradata does not have a similar concept to Oracles' Rowid. You will have to do an Insert/ Select Distinct. |
130 | 03 Dec 2009 @ 04:02 PST | Database | Reply | Macros - Create table | You 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... |
129 | 02 Dec 2009 @ 08:16 PST | Database | Reply | Equivalent for db2 Chr function to handle line feed and tab | set V_TEXT = oreplace (V_TEXT, 'abc;', '0a'XC) ;set V_TEXT = oreplace (V_TEXT, 'def9;', '09'XC);XC is hexadecimal value as a character |
128 | 27 Nov 2009 @ 05:36 PST | Tools | Reply | Tearadata Query and Result using Export Utility | If 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. |
127 | 27 Nov 2009 @ 05:32 PST | Tools | Reply | How 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. |
126 | 27 Nov 2009 @ 02:21 PST | Database | Reply | Comment string is longer than permitted | Maximum 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. |
125 | 26 Nov 2009 @ 05:21 PST | UDA | Reply | Stored Procedure or Sql | Definitely 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... |
124 | 24 Nov 2009 @ 01:01 PST | UDA | Reply | How 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! |