48 | 25 Aug 2009 @ 04:50 PDT | Analytics | Reply | OReplace function | Just give the replace string as a zero length string and it removes the character.So if col1 contains '123*45*56*67*'and you:oreplace (col1,'*','')the result is 123455667 |
47 | 19 Aug 2009 @ 04:30 PDT | Tools | Reply | Suppressing column names in Reports | You have to do it for every column by giving each column a title of:(Title '')If you want to output data instead, then bteq will output a two byte length for every varchar field output. (Dont forge... |
46 | 17 Aug 2009 @ 04:35 PDT | Database | Reply | how to read data from excel file onto Teradata | I presume it is a single worksheet!Save the sheet as a text tab delimited file. If there is more than one sheet, you must save each sheet to a separate file and load them individually.In SQL Assist... |
45 | 13 Aug 2009 @ 05:42 PDT | Tools | Reply | Query that uses more than 100 columns not working properly through bteq | You cannot overcome the restriction on an Export Report.There are two answers - use a Foldline to get the output on multiple lines. You can then put them back together using Perl or sed.Or Export D... |
44 | 12 Aug 2009 @ 06:52 PDT | Database | Reply | Ignore Alphanumeric data content | Unfortunately, Teradata does not support Unix-like regular expressions, so backlash does not do an escape.If one of the characters you want to search-for/ exclude is a %, you need:And Col1 Not like... |
43 | 11 Aug 2009 @ 04:04 PDT | Database | Reply | Ignore Alphanumeric data content | And Col1 Not like any ('%(%' ,'%.%' ,'%''%' ,'%,%' ,'%)%' ,'%>%' , '%;%' , .....) |
42 | 11 Aug 2009 @ 12:56 PDT | Tools | Reply | how to bypass bad record in Multiload | If you extract/ load the data as fixed width, instead of character delimited, yu do not have the same problem.
The only other approach is to change your delimiter to something which does not or ca... |
41 | 28 Jul 2009 @ 02:29 PDT | Database | Reply | Load TAB delimited data via Fastload | .Set Record Vartest "^"You hit the tab key where you see the ^ above.It is sort of WYSIWIG, except in this case it is What you dont see is what you get! |
40 | 24 Jul 2009 @ 02:31 PDT | Tools | Reply | MLoad data handling question | You can use pretty much any SQL functions in Mload, except things like aggregates or Olap.If your input field comes in as char in the format you specified, just load it as you would any other data ... |
39 | 23 Jul 2009 @ 04:03 PDT | Tools | Reply | Clarifications | The .SET sets a multiload variable.These are referenced by prefixing the variable name by &; if you want a period in the output then you code two periods.SoTABLES &DBASE_TARGETTABLE..&TARGETTABLEbe... |
38 | 23 Jul 2009 @ 03:53 PDT | Tools | Reply | Conditional Statements in Fastload | Not in Fastload; you can in Multiload/ TPump. |
37 | 23 Jul 2009 @ 03:50 PDT | Tools | Reply | Using Parameter in BTEQ | 1. Set up a file with the date parameter in (as a character string).So TestFile.txt contains a single line with:2009-01-022. Import his file every time you want to use it. So:.Import Report file = ... |
36 | 23 Jul 2009 @ 03:16 PDT | Tools | Reply | Duplicates in Fastload | You cannot see them in Fastload at all. The only indication it has happened is:Records Read: 110Records Loaded: 100and no errors!If it is a real prblem, and you do not have too many records, use Mu... |
35 | 23 Jul 2009 @ 12:21 PDT | UDA | Reply | Rank | Get the Row_Number (NOT Rank) of the unique Lists_Id.So: SELECT D1.List_Id_Rank,bcp.LISTS_ID as List_Name,CASE WHEN bcp.LISTS_ID = 'A' THEN SUBSTR(LISTS_ATT_ID,1,12) WHEN bcp.LISTS_ID = 'B' THEN SU... |
34 | 23 Jul 2009 @ 09:41 PDT | UDA | Reply | Difficulty Inserting records into Large table | If the table has a NUPI with lots of records with the same PI value (eg an account number for transactions), then Multiset will definitely help. It will bypass the duplicate row check which is almo... |
33 | 23 Jul 2009 @ 02:27 PDT | Analytics | Reply | small SQl query | Teradata does not have a Transform or Pivot function - they are regarded as presentation level functions and it relies on the presentation tool (Cognos/ Access/ BO/ etc) to do that.The SQL below wi... |
32 | 23 Jul 2009 @ 02:07 PDT | UDA | Reply | how to load data without single quotes | Using Fastload, you will have to load the quotes and then strip them off.If you use Multiload, you can put a CASE or substring statements around the input fields.For example:Insert into tbl( col1, ... |
31 | 23 Jul 2009 @ 01:56 PDT | UDA | Reply | transpose | Select Id , exam1 As exam, mar1 as marksfrom tblUnionSelect Id, exa2, mark2Where exa2 is not nullfrom tbl; |
30 | 09 Jul 2009 @ 07:33 PDT | Analytics | Reply | Question on calendar week | The Calendar is a view within the Sys_Calendar database.The code below will rebase Day_Of_Week to start on Monday, not Sunday.I suggest you set up a CALENDARTMPL (for Local) and a CalendarL view in... |
29 | 09 Jul 2009 @ 02:58 PDT | Database | Reply | Replacing Macros Problem | Check the obvious first:1. Make sure you are not running in ANSI mode. (Select * From DBC.Sessioninfo Where Username = User - The TransactionMode should be set to 'T')2. If you are running in Bteq,... |
28 | 09 Jul 2009 @ 02:34 PDT | Database | Reply | Need a logic to retireve data from the table .. | You need to check your requirement against expected results!The following SQL gives the answer:Id Dept CalcSal200 b 20300 c 1200400 d 40This is because:1. Id 300 has departments C and D. 30 * 40 is... |
27 | 28 Jun 2009 @ 06:12 PDT | Analytics | Reply | Generate errors for intersection of queries | Just switch INTERSECT set operator to MINUSselect A,b from ABC MINUSselect B,d from XYZ;.If ActivityCount > 0 Then .Quit 99 |
26 | 28 Jun 2009 @ 06:05 PDT | Analytics | Reply | To know roll access | UserRights will give you all the objects/rights to which you explicitly have access.UserRoleRights gives you all the objects to which you have access via a role.So you can just union these views.A ... |
25 | 26 Jun 2009 @ 02:55 PDT | UDA | Reply | Seaching for particular databases | Select Databasename from DBC.TablesWhere Chars(Trim(Databasename)) = 8 And Substr(Databasename,5,1) Between '0' And '9' And Substr(Databasename,6,1) Between '0' And '9' And Substr(Databasename,7,1)... |
24 | 25 Jun 2009 @ 11:36 PDT | Tools | Reply | Export output file formatting | You cannot do it if you are exporting a DATA file.If you can do an EXPORT REPORT, you can:.SEPARATOR '|'(or whatever separator you want.)If you do this, everything will come out as characters, you ... |