b¬äd†ødd

OAF Reset When

May 16, 2012 by Brad

With Teradata 13 new functions are available. Some are noteworthy even when you do not have an immediate specific application for them. However, sooner or later you find a case where you need them. This was true for me with the Ordered Analytical Function's RESET WHEN capability.

RESET WHEN allows you to reset/clear an accumulating value (e.g. a rolling sum of paid dollars) within the OAF when a defined case is met. But when would you really use it?

My need was to load a small temporal table with phone numbers and keys from a much larger table that had many other superfluous fields as well. Additionally, we want to only include the records where the telephone number changed from its chronologically prior value. And, at the end of the process we have to re-chain the historical valuations appropriately.

The challenge was that some of the records had a blank telephone number value--a surrogate null, if you will. In the case where the value changes from a non-blank value to a blank value it is, in essence, a logical delete (i.e. the physical record remains preserving the valuation chaining, but has an indicator noting it as a delete). To add complexity to the relatively simple scenario, we do not want to put a blank record (i.e. logical delete) out as the first chronological valuation record for the non-temporal key--rather we want to omit it altogether.

To perform both our initial logical delete exclusion and our record change detection processes we use OAF's to leverage their chronological sequencing power. Plus, we do not have to do table self-joins--far more efficient.

Here's what that part of the code looks like.

qualify
    -- the first chronological record
   (row_number() over(partition by key_id
                      order 	by row_eff_dte
                      reset when phon_num = '') = 1
    and
    -- omit empty values
    phon_num <> '')
    or
    -- change detection
    phon_num <> min(phon_num) over(partition by key_id
                                   order     by row_eff_dte
                                   rows between 1 preceding
                                            and 1 preceding)

Now we can find the first chronological record without an empty telephone number value, start from that point and go forward.

Cheers
-Brad