#DateForumTypeThreadPost
235325 Oct 2013 @ 12:35 PDTDatabaseReplyTranspose rows to columnsHi Sarah, definitely don't try an SP,  otherwise 50 columns will probably be processed most performant using a cross join. If this has to be done repeatedly better use a permanent table ...
235223 Oct 2013 @ 04:14 PDTDatabaseReplycould not convert from character to date typeI prefer LIKE over POSITION, it's simpler :-) CAST(CASE WHEN x LIKE '_/_/____' THEN '0' || SUBSTRING(x FROM 1 FOR 2) || '0' || SUBSTRING(x FROM 3 FOR 6) ...
235123 Oct 2013 @ 08:07 PDTToolsReplyNeed to include column heading in Teradata Bteq script This is not a working BTEQ script, there's no BEGIN EXPORT in BTEQ. Simply write a select returning a concatenated list of all column name before you run your actual select: SELECT 'VN...
235023 Oct 2013 @ 08:01 PDTDatabaseReplyTranspose rows to columnsHi Sarah, instead of UNION (better UNION ALL to avoid DISTINCT processing) you can cross join to a table(query returning one row per column: CREATE VOLATILE TABLE vt ( i INT) ON COMMIT PRESERVE...
234922 Oct 2013 @ 11:54 PDTDatabaseReplyconverting interger to date format and comparing with current_date  case when accounting_month = extract(year from current_date) * 100 + extract(month from current_date) then 1 else 0 end There's a slighly shorter ...
234821 Oct 2013 @ 04:38 PDTDatabaseReplyPartitioning query with a reset countWhat's your TD release? There have been multiple threads on normalising overlapping periods and your approach seems overly complex. Could you please post the original data and the expected re...
234718 Oct 2013 @ 10:09 PDTGeneralReplyArcmain Failure 2843:No more room in database.Every table in Teradata consists of subtables (e.g. table header, primary data, fallback data, secondary indices), thus it's using perm space in the table's database. Dieter
234618 Oct 2013 @ 03:37 PDTDatabaseReplyNeed help on UPDATE to INSERT - SELECTNobody wants to read a bunch of unformatted code without any additional details. You can probably rewrite it to a single INS/SEL, it seems to be just another LEFT JOIN.   Dieter
234517 Oct 2013 @ 11:52 PDTDatabaseReplyNeed a sql help to check when multiple rows exists if match found then do alternate match found.You probably need to join the first two tables MEMB_ENRLT_FACT.memb_skey = MEMB_ENRLT_FACT.grp_skey without additional condition and use a QUALIFY ROW_NUMBER with ORDER BY MEMB_ENRLT...
234417 Oct 2013 @ 11:44 PDTDatabaseReplydiff between USI and NUSIHi Ratnam, FastLoad is for loading empty tables, so there's no need to support secondary indexes as you can simply created them after load.   Dieter  
234317 Oct 2013 @ 11:39 PDTDatabaseReplySub query vs OLAP approachHi Khurram, i don't have this exact version, but when you read on you will find a remark that this is a only description how a result set is created logically:   Outer references be...
234217 Oct 2013 @ 09:07 PDTDatabaseReplySELECT with EXISTS (Subquery) in TeradataYou must use an older version of Teradata, this syntax should be supported since 13.10. But it's definitely not the best way to get that info, and i doubt that it's best case in any other ...
234117 Oct 2013 @ 08:55 PDTExtensibilityReplyOracle functions in TD 14Hi Wim, those new functions in TD14 are called "Embedded Services System Functions" or "FastPath UDFs", they're comparable to built-in functions, only Teradate R&D can ...
234016 Oct 2013 @ 11:50 PDTDatabaseReplyIdentifying IDs for duplicate namesBased on your explanation this might be what you need: select min(User_ID) over (partition by First_Name,Last_Name,Birth_Date,Post_Code) as minID ,t.* from tab qualify count(*)...
233916 Oct 2013 @ 11:33 PDTGeneralReplyArcmain Failure 2843:No more room in database.When you do an online backup any inserts/updates/deletes on a table while the backup is running are recorded in a special subtable, thus the tablesize might grow leading to 2843.   Dieter
233815 Oct 2013 @ 01:15 PDTAnalyticsReplyMAX, SUM OLAP FunctionsHi Peter, the QUALIFY is applied *after* the calculation of the OLAP SUM, you probbaly need to use a Derived Table to calculate the ROW_NUMBER first and the apply the SUM on it. Are there multupl...
233712 Oct 2013 @ 01:26 PDTDatabaseReplyIf else and select together in macro/SPYou probably use SQL Assistant and there was a problem related to that (i can't remember which release).  Try to upgrade to a newer release/patch level or try connecting with .NET instead...
233611 Oct 2013 @ 03:01 PDTGeneralReplyNot able to sample the data in UNIONYou get this error because TOP/SAMPLE is not allowed in a UNION :-) But there's a workaround: enclose the selects in Derived Tables: SELECT * FROM ( SELECT TOP 10 A,B,C FROM Tab...
233511 Oct 2013 @ 02:53 PDTDatabaseReplyArcmain and BLCHi Chet,  it's #2, in the current releases BLC is only on disk and when you backup a table it will be uncompressed. That's why you should additionally apply MVC which is kept compres...
233411 Oct 2013 @ 02:43 PDTDatabaseReplyMinmum Time field selection - TIME(6)Do an ORDER BY act_tm and you'll see what happened :-) TIME is internally stored as UTC and sorted based on this, MIN returns the minimum value based on this order and your session timezo...
233310 Oct 2013 @ 11:49 PDTDatabaseReplyInput : Pipe Delimited String in one field - Output : To split the string to different fieldsWhat's your TD release? Are there any UDFs like INSTR/STRTOK?   Dieter
233210 Oct 2013 @ 11:44 PDTDatabaseReplyCasting to An integer giving incorrect result.Hi Rakesh, you should open an incident. Dieter    
233110 Oct 2013 @ 11:36 PDTDatabaseReplyIf else and select together in macro/SP#1: you don't need if/else in that case, simply return an error message for invalid dates:   replace macro mymacro (inDate date) as ( abort 'inDate should be within the last 60 da...
233010 Oct 2013 @ 11:19 PDTDatabaseReplySQL assistant - adding different serversYou can't access multiple Teradata servers within a single session.   Dieter
232910 Oct 2013 @ 11:18 PDTDatabaseReplyConcatanation of the rows from one columns substring(trim(col) from 1 for 3)) Dieter

Pages