Problem with history table

Database
Enthusiast

Problem with history table

Hi, I am trying to build history table which contains all the changes for a event. The columns(column_s, column_o, column_m , column_r) are from 4 different tables which describe the events for a particluar key. The columns(Key and date) are common in all the tables. For a particluar key and date  if the column(let's say column_s) of the current row is null, then the column value(s1) prior to the current row should be populated in the current row. So if we select the key and latest date time stamp from the table that gives the currernt status information of that key.

Table Information:-

All the column data types are Varchar except for date column which is timestamp.

Note: These are fictious column names. Any kind of help is highly appreciated.

Code:-

I am using the below code, but iam not getting the expected output.

SELECT 

KEY

,DATE

,CASE WHEN column_s IS NULL THEN MAX(column_s) OVER(PARTITION BY KEY,DATE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 

          ELSE column_s END AS column_s

FROM

history

Input:-

Key Date                           colums_s  column_o  column_m  column_r

101 5/19/2015 13:15:03  s1            null            null            null

101 5/19/2015 13:15:17  null          null            m1             null

101 5/19/2015 13:16:23  s2            null            null             null

101 5/19/2015 13:17:01  s3            null            null             null

101 5/19/2015 13:17:21  null          o1              null             r1

101 5/19/2015 13:17:23  s4            null            null             null

Expected Output:-

Key Date                           colums_s  column_o column_m column_r

101 5/19/2015 13:15:03  s1            null           null           null

101 5/19/2015 13:15:17  s1            null           m1            null

101 5/19/2015 13:16:23  s2            null           m1            null

101 5/19/2015 13:17:01  s3            null           m1            null

101 5/19/2015 13:17:21  s3            o1             m1            r1

101 5/19/2015 13:17:23  s4            o1             m1            r1

6 REPLIES
Senior Apprentice

Re: Problem with history table

What's your Teradata release?

In TD14.10 there's LAST_VALUE:

   LAST_VALUE(colums_s IGNORE NULLS) 
OVER (PARTITION BY key_col
ORDER BY date_col
ROWS UNBOUNDED PRECEDING)

Before it's more complicated...

Enthusiast

Re: Problem with history table

We are using teradata 13.0.

Is there any way we can do without using Last_Value, becoz in 13 version we dont have that fucntion

Thanks

Sitara

Senior Apprentice

Re: Problem with history table

There's RESET WHEN, but this is way less performant:

   MIN(colums_s) 
OVER (PARTITION BY key_
ORDER BY ts RESET WHEN colums_s IS NOT NULL)
Enthusiast

Re: Problem with history table

Hi Dieter,

Thanks for the inputs, The query is working as expected. The data is in production, and i have to repeat the same functionality for 4 other columns, which will consume more cpu as result the performance will be poor. Is there any way where we can improve performance with out effecting the result.

Sitara

Senior Apprentice

Re: Problem with history table

Hi Sitara,

there's no other way to get this result, that's why there's FIRST_VALUE/LAST_VALUE.

Btw, TD13 is out of maintenance for a while, are you sure there's no planned upgrade?

Enthusiast

Re: Problem with history table

Hi Dieter,

We will be moving to TD14 soon. Thanks a lot Dieter, you made my day.

Sitara