I'm looking to find a simple way, if possible, to select the previous non-null value for a field, where 'previous' is based on a date field. For example, if the field is null on October 4, but is not null on October 3, then the October 3 value would be selected.
This would be synonymous to the last_value(colname ignore nulls) in Oracle. There's about 10 fields I need to do this for, in several places in a stored procedure, and every solution I've worked towards is very slow.
The optimal solution would be something like this:
SELECT ID_COL, REF_COL (SELECT TOP 1 REF_COL FROM CSESKB.IVA_STATUS_RANGE TBL2 WHERE TBL2.DATE_COL <= TBL1.DATE_COL AND REF_COL IS NOT NULL ORDER BY DATE_COL ) NEW_REF_COL, DATE_COL, FROM TBL1 WHERE (conditions)
But Teradata won't allow SELECT statements as column definitions. Using cursors to build up a temp table is too slow, ROW_NUMBER() and MAX() won't ignore nulls, and multiple left outer joins are slower than I'd like.
Is there a simple and fast solution that I'm just missing here, or is this going to require more complicated logic?
Well, no reply, but I believe I worked out a pretty passable solution, so I figured I'd post it in case anyone in the future has a similar problem. I concatenate the field in question with the date field, and then find the max of that field ordered by date with all rows earlier than the row in question. Then it's a simple matter of pulling the concatenated part back out.
ID_FIELD, DATE_FIELD, VALUE_FIELD, MAX(CASE WHEN VALUE_FIELD IS NULL THEN '0001-01-01 00:00:00' ELSE DATE_VAL_MRG END) OVER (PARTITION BY ID_FIELD ORDER BY DATE_FIELD ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) MAX_PREV_DATE_VAL
(SELECT A.*, CAST(DATE_FIELD AS VARCHAR(19))||COALESCE(VALUE_FIELD,' ') DATE_VAL_MRG FROM TBL1 A ) MERGE_FIELDS
ORDER BY ID_FIELD, DATE_FIELD
Thanks to anyone who may have read this and at least gave it some thought.
This is an old SQL-trick :-) but probably the best solution for your problem. At least it's the only one without nested OALP-functions, i once tried to find a better solution, but never suceeded. As LAST_VALUE is part of Standard SQL:2008 now, hopefully Teradata will implement it.
Just one remark, if you remove those nested Derived Tables you'll get a better plan. And there'ss no need for COALESCE/CASE SELECT ID_col, DATE_col, COALESCE(SUBSTR(MAX(CAST(DATE_col AS CHAR(10))||ref_col) OVER (PARTITION BY ID_col ORDER BY DATE_col ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 10) ,'N' ) AS VALUE_FINAL FROM TBL1 A