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.
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.
I am using the below code, but iam not getting the expected output.
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...
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
There's RESET WHEN, but this is way less performant:
OVER (PARTITION BY key_
ORDER BY ts RESET WHEN colums_s IS NOT NULL)
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.
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?