#DateForumTypeThreadPost
342814 Jan 2015 @ 04:32 PSTDatabaseReplyDelete statement consuming more resource Hi Karthick, if you need to be able to rollback the delete there's no other way than both delete and ins/sel in one transaction. But then you have to pay the price, in worst case the ins/sel ...
342714 Jan 2015 @ 03:38 PSTDatabaseReplyDelete statement consuming more resource Hi Karthick, what about two macros or a Stored Procedure?
342614 Jan 2015 @ 03:08 PSTDatabaseReplyDelete statement consuming more resource Hi Karthick, the solution is self-evident, use seperate statements/transactions instead of a macro. 
342514 Jan 2015 @ 01:38 PSTDatabaseReplyDelete statement consuming more resource Hi Karthick, assuming the delete is a DELETE ALL followed by an INS/SEL: DELETE ALL is fast if it's the last statement within a transaction and the optimizer knows it will be commited (n...
342413 Jan 2015 @ 11:27 PSTDatabaseReplyHelp coding on count historical event within a fix time period, greatly thanks!Hi Vrushang, based on the description the result should be the number of rows within 60 minutes before the current row's eventtime, i.e. a moving count (not cumulative as I wrote in my first p...
342313 Jan 2015 @ 09:52 PSTDatabaseReplyRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGHi Moh, for UNBOUNDED there's no difference between RANGE and ROWS :) Otherwise a possible workaround depends on what you actually need as a result...   E.g. there is another topic&nbs...
342213 Jan 2015 @ 09:47 PSTDatabaseReplyHelp coding on count historical event within a fix time period, greatly thanks!And for user_1 1 and eventtime 2:40 it should return 3, too? I don't think there's an easy way to get this result, even a cursor or recursive query will fail. Some fancy SQL using cross j...
342113 Jan 2015 @ 09:35 PSTAnalyticsReplystandard deviation function with parition byI don't think that the ODBC driver changed the STDDEV_SAMP, but maybe a calculation of a column used within, that's why I was asking for the exact error message and checking DBQL :-)
342013 Jan 2015 @ 06:27 PSTAnalyticsReplystandard deviation function with parition byWhat's the exact error message? Can you extract the actual SQL submitted from DBQL, maybe it was modified by the ODBC driver. Try if checking "Disable Parsing" in the ODBC datasourc...
341913 Jan 2015 @ 02:04 PSTDatabaseReply2616 error numeric overflow with row_number() It's a cumulative sum of 1s :-)   But I just noticed that the COUNT was wrong, should also include ROWS: CAST(COUNT(*) AS BIGINT) OVER (ORDER BY col_name ROWS UNBOUNDED PRECEDING) &n...
341813 Jan 2015 @ 01:57 PSTDatabaseReplyFind position of first non-digit or space from the backHi Eric, are there any UDFs at your site or is there any chance you can install one?
341713 Jan 2015 @ 01:54 PSTDatabaseReplyALTER TO CURRENTI don't know exactly, but this is probably related to CURRENT_DATE being dependent on your session time zone: BTEQ -- Enter your SQL request or BTEQ command: SET TIME ZONE -11; SELECT C...
341613 Jan 2015 @ 01:49 PSTDatabaseReplyFind position of first non-digit or space from the backHi Bhaskar, it's a substring based on a Regular Expression, TD14 also supports REGEXP_SIMILAR (extended LIKE), REGEXP_INSTR (extended POSITION), REGEXP_REPLACE and REGEXP_SPLIT_TO_TABLE (split...
341513 Jan 2015 @ 01:41 PSTAnalyticsReplySyntax error due to Stored Proc ParameterTime zones are always hard to deal with :-) Additionally different systems might return different results based on some internal settings...   The easiest way should be changing the session...
341412 Jan 2015 @ 11:28 PSTDatabaseReply2616 error numeric overflow with row_number() Seems like the resulting datatype of ROW_NUMBER can't be changed (of course INTEGER is quite stupid), but ROW_NUMBER is just a shortcut for a COUNT: CAST(COUNT(*) AS BIGINT) OVER (ORDER BY c...
341312 Jan 2015 @ 10:13 PSTDatabaseReplyTeradata - to solve a data issue with query or ProcedureThis is a simple task for recursion: WITH RECURSIVE cte (Col1, Col2, minCol) AS ( SELECT Col1, Col2, Col1 AS minCol FROM tab WHERE Col1 = Col2 UNION ALL SELECT ...
341212 Jan 2015 @ 07:00 PSTDatabaseReplyRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGRANGE syntax is not implemented in Teradata, you need to user ROWS instead: FIRST_VALUE (Col_name) OVER (PARTITION BY ACCOUNT_ID ORDER BY ACCOUNT_ID , VALID_FROM ROWS BETWEEN UNBOUNDED PRE...
341112 Jan 2015 @ 03:55 PSTAnalyticsReplystandard deviation function with parition byOf course it's working, what's your exact error message? "is not a valid Teradata SQL token" indicates some strange character in your source code.
341011 Jan 2015 @ 11:31 PSTAnalyticsReplySyntax error due to Stored Proc ParameterThis should work, assuming there's no nov. 31 down under und it should december instead :-) SELECT TIMESTAMP '2014-12-31 13:34:56+00:00' AS Completed_Dt, Completed_Dt AT '...
340911 Jan 2015 @ 01:53 PSTDatabaseReplySyntax when concatenating string and numberYou can't store leading blanks in a numeric column, but every column in Teradata has a default format applied when casted. You you can change it in the CREATE TABLE: Store INTEGER FORMAT ...
340811 Jan 2015 @ 10:50 PSTDatabaseReplySyntax when concatenating string and numberSeems you got a space character between after the first "|", remove it to get "||" instead of "| |"   And instead of directly concatenating Store/ID which ...
340710 Jan 2015 @ 07:31 PSTDatabaseReplyChange data captureHi Larry, both joining on COALESCE and MINUS will result in redistribution steps, you better switch to NOT EXISTS instead: SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE (t1.co...
340610 Jan 2015 @ 04:16 PSTDatabaseReplyHow to create a loopThis is a simlpe task for Standard SQL's Windowed Aggregate Functions, you need a cumulative sum: select WK ,Year ,sum(costA) over (partition by year order by wk ...
340509 Jan 2015 @ 01:30 PSTDatabaseReplyFind position of first non-digit or space from the backWhat's your TD release? Regular expressions can easily extract both fields looking for a group of digits at the end of the string: field1: REGEXP_SUBSTR(txt, '.+?(?=[0-9]*$)') f...
340409 Jan 2015 @ 01:04 PSTAnalyticsReplySyntax error due to Stored Proc ParameterWhen you check DBQL you'll find that a closing bracket is removed from the SQL code submitted within the SP, seems to be a strange parser bug. What are you trying to do wtih all those CASTs? ...

Pages