4228 | 08 Mar 2016 @ 01:41 PST | Database | Reply | Add overlapping days covered with different ID types | I 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
|
4227 | 08 Mar 2016 @ 09:48 PST | Database | Reply | Add overlapping days covered with different ID types | That'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... |
4226 | 07 Mar 2016 @ 05:56 PST | Database | Reply | DateAdd 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... |
4225 | 05 Mar 2016 @ 02:08 PST | Database | Reply | How do i write qualify rank() over (partition.....question | It's available since TD14.10, before you need to rewrite it:
Missing Functions: DENSE_RANK
|
4224 | 05 Mar 2016 @ 02:08 PST | Database | Reply | Help with SQL for reporting month totals | What'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 ... |
4223 | 04 Mar 2016 @ 02:47 PST | Database | Reply | Query to remove some characters from a column | You 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... |
4222 | 03 Mar 2016 @ 11:24 PST | Database | Reply | need to test a query using three diff dates | It's hard to help without knowing what you try to do.
You need to add more details including some sample data and expected result...
|
4221 | 03 Mar 2016 @ 11:18 PST | Database | Reply | Count rows for every x minute interval | You 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) ... |
4220 | 03 Mar 2016 @ 07:39 PST | Tools | Reply | How to extract the count for the odbc source records in TPT | Afaik 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... |
4219 | 03 Mar 2016 @ 07:25 PST | Tools | Reply | How to extract the count for source & target records in TPT | MERGE 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... |
4218 | 03 Mar 2016 @ 07:20 PST | Database | Reply | Why 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)
|
4217 | 02 Mar 2016 @ 07:24 PST | Database | Reply | Converting string to date using REGEXP - Getting error 3798 | Hi 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, &... |
4216 | 02 Mar 2016 @ 06:47 PST | Database | Reply | Converting string to date using REGEXP - Getting error 3798 | Hi 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... |
4215 | 02 Mar 2016 @ 04:44 PST | Database | Reply | Converting string to date using REGEXP - Getting error 3798 | Wow, 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... |
4214 | 01 Mar 2016 @ 11:40 PST | Database | Reply | Use the outcome of a query as input for a new select statement | It'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... |
4213 | 29 Feb 2016 @ 09:21 PST | Database | Reply | How 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... |
4212 | 25 Feb 2016 @ 02:14 PST | Database | Reply | Data 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.... |
4211 | 24 Feb 2016 @ 11:53 PST | Database | Reply | Performance of corelated subquery | What kind of Correlated Subqueries?
Scalar within the SELECT list?
Using non-equality conditions?
|
4210 | 24 Feb 2016 @ 11:51 PST | Database | Reply | teradata 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.
|
4209 | 24 Feb 2016 @ 11:44 PST | Database | Reply | Recursive SQL with update query | WITH 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... |
4208 | 24 Feb 2016 @ 11:33 PST | Connectivity | Reply | How to block all client connections to teradata database from a particular server/IP | Yep, you can specify a client IP-address in the Request Source classification.
|
4207 | 24 Feb 2016 @ 11:28 PST | Tools | Reply | Convert 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)... |
4206 | 24 Feb 2016 @ 11:19 PST | Database | Reply | Recursive 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.
|
4205 | 24 Feb 2016 @ 11:15 PST | Database | Reply | Tactical Queries | Did 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... |
4204 | 24 Feb 2016 @ 09:34 PST | Teradata Applications | Reply | Import 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";
|