Find previous non-null value

Analytics
Enthusiast

Find previous non-null value

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?

Thanks for your time
3 REPLIES
Enthusiast

Re: Find previous non-null value

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.

SELECT

ID_FIELD,
DATE_FIELD,
COALESCE(COALESCE(VALUE_FIELD,SUBSTR(MAX_PREV_DATE_VAL,20,1)),'N') VALUE_FINAL

FROM

(SELECT

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

FROM

(SELECT
A.*,
CAST(DATE_FIELD AS VARCHAR(19))||COALESCE(VALUE_FIELD,' ') DATE_VAL_MRG
FROM TBL1 A
) MERGE_FIELDS

) GET_MAX

ORDER BY ID_FIELD, DATE_FIELD

Thanks to anyone who may have read this and at least gave it some thought.
Senior Apprentice

Re: Find previous non-null value

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

Dieter
Enthusiast

Re: Find previous non-null value

Oh wow, that's a lot better. Thanks for your input. It will definitely be nice if they implement last_value.