Help with Last_value Window Function in Teradata

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

Help with Last_value Window Function in Teradata

Hi ,

 

I am looking for a solution on the below problem. I have the following logic in Hadoop which is interpreted differently than in TD and i need to replicate the same functionality in TD.

 

 

last_value (case when activity_code in (100,9200,4100,10500,10600) then dw_case_activity_key else null end, true) over (partition by incident_number order by activity_date rows between unbounded preceding and 1 preceding) as prior_work_act

If you look at the above logic, the case statement is applied for the entire window aggregation group and not for a single record. In TD however the case statement is being evaluated for each record and not on the aggregation range/group.

 

 

How do i rewrite the same LAST_VALUE function that is applicable on the aggregation group. Below is my  example from Hadoop .

 

INPUT DATA :

dw_case_activity_keyincident_numberactivity_codeactivity_date
ABC6861657366002/14/2019 19:27:37
DEF68616573617002/14/2019 19:27:39
GHI68616573635002/14/2019 19:27:41
JKL68616573617002/14/2019 19:27:46
MNO68616573692002/14/2019 19:27:48
PQR68616573635002/14/2019 19:28:15
STU6861657363002/14/2019 19:28:35
VWX68616573641002/14/2019 19:28:35
YZA168616573635002/14/2019 20:07:25
B1C1D16861657363002/14/2019 20:07:40

 

Output in Hadoop

dw_case_activity_keyincident_numberactivity_codeactivity_dateprior_work_act
ABC6861657366002/14/2019 19:27:37NULL
DEF68616573617002/14/2019 19:27:39NULL
GHI68616573635002/14/2019 19:27:41NULL
JKL68616573617002/14/2019 19:27:46NULL
MNO68616573692002/14/2019 19:27:48NULL
PQR68616573635002/14/2019 19:28:15MNO
STU6861657363002/14/2019 19:28:35MNO
VWX68616573641002/14/2019 19:28:35MNO
YZA168616573635002/14/2019 20:07:25VWX
B1C1D16861657363002/14/2019 20:07:40VWX

 

How ever the same logic written as is in Teradata is giving me the following output

 

Output in Teradata 

dw_case_activity_keyincident_numberactivity_codeactivity_dateprior_work_act
ABC6861657366002/14/2019 19:27:37NULL
DEF68616573617002/14/2019 19:27:39NULL
GHI68616573635002/14/2019 19:27:41NULL
JKL68616573617002/14/2019 19:27:46NULL
MNO68616573692002/14/2019 19:27:48NULL
PQR68616573635002/14/2019 19:28:15MNO
STU6861657363002/14/2019 19:28:35NULL
VWX68616573641002/14/2019 19:28:35NULL
YZA168616573635002/14/2019 20:07:25VWX
B1C1D16861657363002/14/2019 20:07:40NULL

 


Accepted Solutions
Ambassador

Re: Help with Last_value Window Function in Teradata

To get the expected result you need to replace Hive's non-Standard SQL true syntax with IGNORE NULLS

last_value (case when activity_code in (100,9200,4100,10500,10600) then dw_case_activity_key else null end IGNORE NULLS) 
over (partition by incident_number 
      order by activity_date 
      rows between unbounded preceding and 1 preceding) as prior_work_act
1 ACCEPTED SOLUTION
2 REPLIES 2
Ambassador

Re: Help with Last_value Window Function in Teradata

To get the expected result you need to replace Hive's non-Standard SQL true syntax with IGNORE NULLS

last_value (case when activity_code in (100,9200,4100,10500,10600) then dw_case_activity_key else null end IGNORE NULLS) 
over (partition by incident_number 
      order by activity_date 
      rows between unbounded preceding and 1 preceding) as prior_work_act
Enthusiast

Re: Help with Last_value Window Function in Teradata

Thank you so much Dieter, you solved my issue.