All Forums Database
chava70 9 posts Joined 12/14
28 Jun 2015
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

 

 

dnoeth 4628 posts Joined 11/04
28 Jun 2015

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...

Dieter

chava70 9 posts Joined 12/14
28 Jun 2015

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

dnoeth 4628 posts Joined 11/04
28 Jun 2015

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)

 

Dieter

chava70 9 posts Joined 12/14
28 Jun 2015

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

dnoeth 4628 posts Joined 11/04
28 Jun 2015

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?

Dieter

chava70 9 posts Joined 12/14
29 Jun 2015

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

You must sign in to leave a comment.