#DateForumTypeThreadPost
4825 Aug 2009 @ 04:50 PDTAnalyticsReplyOReplace functionJust 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
4719 Aug 2009 @ 04:30 PDTToolsReplySuppressing column names in ReportsYou 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...
4617 Aug 2009 @ 04:35 PDTDatabaseReplyhow to read data from excel file onto TeradataI 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...
4513 Aug 2009 @ 05:42 PDTToolsReplyQuery that uses more than 100 columns not working properly through bteqYou 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...
4412 Aug 2009 @ 06:52 PDTDatabaseReplyIgnore Alphanumeric data contentUnfortunately, 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...
4311 Aug 2009 @ 04:04 PDTDatabaseReplyIgnore Alphanumeric data contentAnd Col1 Not like any ('%(%' ,'%.%' ,'%''%' ,'%,%' ,'%)%' ,'%>%' , '%;%' , .....)
4211 Aug 2009 @ 12:56 PDTToolsReplyhow to bypass bad record in MultiloadIf 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...
4128 Jul 2009 @ 02:29 PDTDatabaseReplyLoad 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!
4024 Jul 2009 @ 02:31 PDTToolsReplyMLoad data handling questionYou 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 ...
3923 Jul 2009 @ 04:03 PDTToolsReplyClarificationsThe .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...
3823 Jul 2009 @ 03:53 PDTToolsReplyConditional Statements in FastloadNot in Fastload; you can in Multiload/ TPump.
3723 Jul 2009 @ 03:50 PDTToolsReplyUsing Parameter in BTEQ1. 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 = ...
3623 Jul 2009 @ 03:16 PDTToolsReplyDuplicates in FastloadYou 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...
3523 Jul 2009 @ 12:21 PDTUDAReplyRankGet 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...
3423 Jul 2009 @ 09:41 PDTUDAReplyDifficulty Inserting records into Large tableIf 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...
3323 Jul 2009 @ 02:27 PDTAnalyticsReplysmall SQl queryTeradata 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...
3223 Jul 2009 @ 02:07 PDTUDAReplyhow to load data without single quotesUsing 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, ...
3123 Jul 2009 @ 01:56 PDTUDAReplytransposeSelect Id , exam1 As exam, mar1 as marksfrom tblUnionSelect Id, exa2, mark2Where exa2 is not nullfrom tbl;
3009 Jul 2009 @ 07:33 PDTAnalyticsReplyQuestion on calendar weekThe 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...
2909 Jul 2009 @ 02:58 PDTDatabaseReplyReplacing Macros ProblemCheck 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,...
2809 Jul 2009 @ 02:34 PDTDatabaseReplyNeed 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...
2728 Jun 2009 @ 06:12 PDTAnalyticsReplyGenerate errors for intersection of queriesJust switch INTERSECT set operator to MINUSselect A,b from ABC MINUSselect B,d from XYZ;.If ActivityCount > 0 Then .Quit 99
2628 Jun 2009 @ 06:05 PDTAnalyticsReplyTo know roll accessUserRights 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 ...
2526 Jun 2009 @ 02:55 PDTUDAReplySeaching for particular databasesSelect 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)...
2425 Jun 2009 @ 11:36 PDTToolsReplyExport output file formattingYou 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 ...

Pages