#DateForumTypeThreadPost
327807 Nov 2014 @ 11:37 PSTDatabaseReplyQuery on running sum(cumulative diff)-not cdiffThe only way to get the expected result seems to be a recursive select. You need a Volatile Table with a ROW_NUMBER() OVER (PARTITION BY location ORDER BY PROMOTION) as rn and a COUNT(*) ...
327707 Nov 2014 @ 11:30 PSTDatabaseReplyCumulative Average where the partition are overlapping (Moving Avg.)Of course you can do this in a single query, but you need nested OLAP functions. Assuming that month is an actual date (1st of month?) this gets the previous december's value: MAX(CASE...
327607 Nov 2014 @ 10:19 PSTDatabaseReplyMultiple value in a string referring thru a variable.What's your TD release? In TD14 you might utilize STRTOK_SPLIT_TO_TABLE like: WITH cte (col1) as ( select col1 from table1 where <condition> ) sel * from tab2 where col2 in (...
327507 Nov 2014 @ 10:10 PSTDatabaseReplyData skew /qry running longYou need to show the DDL of the view (and the base tables PPI).  There's a UNION in it which causes that huge redistribution, hopefully you can replace it with a UNION ALL.
327407 Nov 2014 @ 09:52 PSTTeradata ApplicationsReplySubstring Function in Teradata StudioHi Anirudh, there's probably no workaround, SUBSTRING('ABC',1,2) is a ODBC SQL extension, which is automatically replaced by the ODBC driver with valid syntax if Disable Parsing&n...
327307 Nov 2014 @ 12:21 PSTDatabaseReplySELECT RANDOM RECORDS from big tableHi Arint, the SAMPLE is not sorting and it redistributes the resulting rows only (the estimated number of rows is just misleading). The TOP indicates you're running an older version of Terada...
327207 Nov 2014 @ 12:12 PSTDatabaseReplyDealing with trailing whitespaceThose spaces were no real spaces (hex 20), run a CHAR2HEXINT(col) to see the actual values
327107 Nov 2014 @ 12:11 PSTDatabaseReplyplease help me in fixing this errorThe round brackets are not balanced: and b.create_ts = a.create_ts ) d There's no opening bracket/Select for d   And there's no need to cast create_ts as a ...
327006 Nov 2014 @ 10:31 PSTDatabaseReplySELECT RANDOM RECORDS from big tableHi Arint, can you show your actual query and an Explain of your query? Both SAMPLE and TOP (without ORDER BY) should not redistribute and/or sort.  
326906 Nov 2014 @ 05:10 PSTDatabaseReplyRegular Expression to check the time formatTry regexp_similar(col,'([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])')  
326806 Nov 2014 @ 05:00 PSTDatabaseReplycoalesec((min(start_dt) over(partition by a_id, b_id order by start_dt asc rows between 1 following and 1 following ))-1,'9999-0Hi Rajendra, it's coalesce instead of coalesec :-)
326731 Oct 2014 @ 04:04 PDTToolsReplyBTEQ export CSV issueHi Rohit, you must write the column header with a seperate Select: sel 'eno,ename,dno,sal' (TITLE '');  
326630 Oct 2014 @ 10:05 PDTDatabaseReplyUsing SI in view with analitical functionLogically WHERE is processed before QUALIFY, so both queries don't return the same result (at least the optimizer doesn't know might be the same). And if you cross join all rows there'...
326530 Oct 2014 @ 10:03 PDTDatabaseReplylength of a stringBtw, in TD14 there's also SQL Server's LENGHT function.
326430 Oct 2014 @ 09:41 PDTDatabaseReplyTablesize in GBThe number of rows in the dbc.DatabaseSpace table should be exactly the same (= number of AMPs in the system) for each and every table on a system. There's at least a table header on each AMP. ...
326330 Oct 2014 @ 09:31 PDTDatabaseReplyOREPLACE What's your TD release? This works fime for me, double check if there's another UDF with the same name, either your defualt database or SYSLIB.
326230 Oct 2014 @ 02:55 PDTToolsReplyBTEQ export CSV issueHi Rohit, you can write any file extension, a REPORT will always export in the same format, readable fixed width text. There's no built in support for delimited export in BTEQ. You have to co...
326128 Oct 2014 @ 01:06 PDTGeneralReplySelect data from one row, while on another row in the same table.Hi Genesius, in your table TDWSTAGE.NJSACECalendar there's a column DoW and in the Select you alias SCC.Day_Of_Week AS DoW. Now when you use DoW the parser has two DoWs to choose from, a...
326028 Oct 2014 @ 09:53 PDTDatabaseReplyMLOAD error code need to be capturedWhat errors are you trying to capture? When you RELEASE MLOAD there will be errors like 2580 MLoad not active, of course you can handle them.
325928 Oct 2014 @ 09:37 PDTToolsReplyDoubts regarding MLOAD scriptWhen you read the MLoad manual you'll notice that the table's PI (plus partition) must be referenced in the WHERE-clause and must not be updated.   And again, what you need is a SQL ...
325828 Oct 2014 @ 09:34 PDTToolsReplyFLOAD Script throws error "TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body"The script you showed is a FastLoad script, you can't run that with TPT/tbuild. There's no FLOAD utility, just MLOAD. Neither FastLoad nor MLoad generate scripts, only FastExport can crea...
325728 Oct 2014 @ 09:28 PDTDatabaseReplyCURRENT PERM AND PEAK PERM IN DBC.TABLESIZEPeakPerm is usually reset to zero in a daily/weekly batch, only when the CurrentPerm increases the PeakPerm is also set. So there were no Inserts in the first table after the last reset.
325628 Oct 2014 @ 09:19 PDTGeneralReplySelect data from one row, while on another row in the same table.Hi Genesius, Holiday holds the name of the holiday but no date, based on your table definition you need to join using CalDate instead.
325528 Oct 2014 @ 08:41 PDTDatabaseReply[6760] Invalid timestampYou can also write a DATE literal: WHERE col = DATE '2012-11-14' or WHERE col = CAST('14/11/2012' AS DATE FORMAT 'dd/mm/yyyy') There's no need for CAST(col AS DATE...
325428 Oct 2014 @ 03:13 PDTDatabaseReply[6760] Invalid timestampThe easiest way to write a Timestamp literal is TIMESTAMP 'yyyy-mm-dd hh:mi:ss': WHERE col = TIMESTAMP '2012-11-14 16:24:18.071000' If you really need to use a different format ...

Pages