#DateForumTypeThreadPost
422808 Mar 2016 @ 01:41 PSTDatabaseReplyAdd overlapping days covered with different ID typesI still don't get it, sounds a bit like this question a few weeeks ago: http://forums.teradata.com/forum/database/calculation-of-prescription-drug-adherence  
422708 Mar 2016 @ 09:48 PSTDatabaseReplyAdd overlapping days covered with different ID typesThat's the same question you posted on SO a few weeks ago :-) You need to add more details: How many rows per member, avg & max? How many members? Is there any rule to determine for whi...
422607 Mar 2016 @ 05:56 PSTDatabaseReplyDateAdd and DateDiff function in Teradata??Hi Peter, as long as the difference between the timestamps is less than 10000 days: SELECT TIMESTAMP '2016-02-03 12:00:00' AS ts1, TIMESTAMP '2016-02-03 14:00:00' AS ts2, ts...
422505 Mar 2016 @ 02:08 PSTDatabaseReplyHow do i write qualify rank() over (partition.....questionIt's available since TD14.10, before you need to rewrite it: Missing Functions: DENSE_RANK
422405 Mar 2016 @ 02:08 PSTDatabaseReplyHelp with SQL for reporting month totalsWhat's your Teradata release? Since 14.10 there's LAST_VALUE: select Member, Elig_dt, -- find the previous gap's row number rn - last_value(case when flag ...
422304 Mar 2016 @ 02:47 PSTDatabaseReplyQuery to remove some characters from a columnYou might also get rid of the oReplace by using three capturing groups within the regex: replace all three groups by the 2nd: REGEXP_REPLACE(original_String , '(%)(\[.+?\])(Q[0-9]+_[0-9...
422203 Mar 2016 @ 11:24 PSTDatabaseReplyneed to test a query using three diff datesIt's hard to help without knowing what you try to do.  You need to add more details including some sample data and expected result...
422103 Mar 2016 @ 11:18 PSTDatabaseReplyCount rows for every x minute intervalYou can use following calculation to truncate a timestamp to half hour intervals:  TS - ((EXTRACT(MINUTE FROM TS) MOD 30) * INTERVAL '1' MINUTE + EXTRACT(SECOND FROM TS) ...
422003 Mar 2016 @ 07:39 PSTToolsReplyHow to extract the count for the odbc source records in TPTAfaik there's no built-in variable (like the ones in MLoad/TPump). Have a look at the Operational Metadata chapter in the TPT User Guide: http://www.info.teradata.com/HTMLPubs/DB_TTU_15_00/Lo...
421903 Mar 2016 @ 07:25 PSTToolsReplyHow to extract the count for source & target records in TPTMERGE can't be used for Inserts into a table with a Identity PI (unless it's a single row). You need to use two statements using old-school UPDATE WHERE EXISTS & INSERT/SELECT WHERE NO...
421803 Mar 2016 @ 07:20 PSTDatabaseReplyWhy Predicate Push Down mechanism doesn`t happen?The optmizer can't push this, because CUSTOMER_MDM_ID is a column in query #1, but the result of a calculation in #2:  COALESCE(rel.CUSTOMER_ID, crdt.CUSTOMER_MDM_ID)  
421702 Mar 2016 @ 07:24 PSTDatabaseReplyConverting string to date using REGEXP - Getting error 3798Hi Greg, I think the defaults for REGEXP_REPLACE changed, it's working fine in 15/15.10, you might try regexp_replace('1925-7-9 4 1 4', '\b([0-9])\b', '0\1', 1, 0, &...
421602 Mar 2016 @ 06:47 PSTDatabaseReplyConverting string to date using REGEXP - Getting error 3798Hi Greg, yep, the "\1" is a back-reference to the first match, i.e. the single digit. Btw, your regex is overly complicated, it only works for exactly "single digit, minus, si...
421502 Mar 2016 @ 04:44 PSTDatabaseReplyConverting string to date using REGEXP - Getting error 3798Wow, you think this is more understandable? :) You can use a simple regex to add a leading zero to a single digit: regexp_replace('1925-7-9', '\b([0-9])\b', '0\1') &nbs...
421401 Mar 2016 @ 11:40 PSTDatabaseReplyUse the outcome of a query as input for a new select statementIt's a horrible data model, that's why you need horrible queries :) select frm.form_name, case lkp.last_name when 'column1' then frm.column1 when 'column2...
421329 Feb 2016 @ 09:21 PSTDatabaseReplyHow to convert UTC millisecond to Timestamp(6)Hi Piotr, this is a modfied Timestamp to Unixtime calculation including centiseconds: REPLACE FUNCTION TimeStamp_to_Unixtime_centisec (ts TIMESTAMP(6)) RETURNS BIGINT LANGUAGE SQL CONTAINS S...
421225 Feb 2016 @ 02:14 PSTDatabaseReplyData set manipulation You didn't tell the actual logic why it's only for those rows. Looks like it's replace PE with the previous non-PE value CASE WHEN text_ = 'PE' THEN 'ape.&#...
421124 Feb 2016 @ 11:53 PSTDatabaseReplyPerformance of corelated subqueryWhat kind of Correlated Subqueries? Scalar within the SELECT list? Using non-equality conditions?
421024 Feb 2016 @ 11:51 PSTDatabaseReplyteradata insert to identity column?There's no syntax like that in Teradata, you can only create the Identity using GENERATED AS DEFAULT. But then you must adjust the MINVALUE to the maximum actual value plus one.
420924 Feb 2016 @ 11:44 PSTDatabaseReplyRecursive SQL with update queryWITH can only be used with SELECT (just don't ask why): Note: You can only use this statement modifier with SELECT statements. WITH cannot be used with other DML statements.   Ther...
420824 Feb 2016 @ 11:33 PSTConnectivityReplyHow to block all client connections to teradata database from a particular server/IPYep, you can specify a client IP-address in the Request Source classification.
420724 Feb 2016 @ 11:28 PSTToolsReplyConvert a Field What's your Teradata release? This works fine for me, but you better use oTranslate instead of oReplace: SELECT CAST(OTRANSLATE('1 12 ', ' ', 'x') AS CHAR(12)...
420624 Feb 2016 @ 11:19 PSTDatabaseReplyRecursive Query to List down the Lineage for a Particular Table with Immediate Parents Hi Himanshu, you probably use the wrong column in your recursive part, old.source instead of new.source.
420524 Feb 2016 @ 11:15 PSTDatabaseReplyTactical QueriesDid you check if you're I/O bound? How do you submit those 100-120 single amp tactical queries within the SP, serially or in parallel? Do the Inserts/Deletes process the same or dif...
420424 Feb 2016 @ 09:34 PSTTeradata ApplicationsReplyImport Tab delimited file through Teradata fast load utility is not working.There's a missing semicolon: .BEGIN LOADING TABLENAME; .RECORD 1; .IMPORT INFILE name_arch FORMAT VARTEXT '09'XCV";  

Pages