Tags
A powerfull feature of the LAST_VALUE/FIRST_VALUE analytic function is the IGNORE NULL clause. This feature allows us to inherit a column value from a prio row with a specific attribute.
Just lately I came accross the following problem on ORACLE SQL & PL/SQL support forum:
The Data:
WITH data (skp_id, code_status, date_app) AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) SELECT skp_id , code_status , date_app FROM data /
The task was to show with every line the last rejection date prior to the current row as an additional column.
SKP_ID | CODE_STATUS | DATE_APP | LAST_REJECTED |
---|---|---|---|
111 | REJECTED | 01-JAN-16 | – |
123 | APPROVED | 01-FEB-16 | 01-JAN-16 |
201 | APPROVED | 01-MAR-16 | 01-JAN-16 |
302 | REJECTED | 01-APR-16 | 01-JAN-16 |
403 | REJECTED | 01-MAY-16 | 01-APR-16 |
450 | APPROVED | 01-JUN-16 | 01-MAY-16 |
475 | APPROVED | 01-JUL-16 | 01-MAY-16 |
480 | APPROVED | 01-AUG-16 | 01-MAY-16 |
490 | REJECTED | 01-SEP-16 | 01-MAY-16 |
To achieve this the LAST_VALUE analytic function with the IGNORE NULLS addition is very handy as it allows to ignore those rows that we are not interested in (the approved ones).
Solution:
In a first step we nullify all rows in the generated last_rejected column that are of no interest:
DECODE(code_status,'REJECTED',date_app)
The next step searches the last entry in the result set where the column value of the generated column is not null.
LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) OVER (ORDER BY skp_id)
And the last step makes sure, that we only evaluate to prior of the current row but not including the current row.
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
The whole statement looks like this:
WITH data (skp_id, code_status, date_app) AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) SELECT skp_id , code_status , date_app , LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) OVER (ORDER BY skp_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as last_rejected FROM data /
Analytic functions are not really new – the came with 8.1.6 – but still they do not get the attraction they deserve.