CODEWITHSUNDEEP
sqloracle-database
Sumit Jain
Sumit Jain

Reputation: 377

Oracle SQL need next row in a table

I am getting blank output in below code. My code is to pull next status after arranging the table in ascending order i.e. whenever the shipment_status_id =32, then it should pull next status. Please help me to fix this or any better alternative.

For Ex:

Shipment_id ---------Status ----------CHANGE_DATE ---------------Employee_id

----- 1234 ---------------211 ---------- 10 Jan 17 12:32 PM ---------------- 1111

----- 1234 ---------------32 ----------- 10 Jan 17 01:32 PM ---------------- 1111

----- 1234 ---------------23 ----------- 10 Jan 17 02:32 PM ---------------- 1111

So in the example, it should give 23 and 10 Jan 17 02:32 PM as the output

Thanks,

 SELECT DISTINCT A41.shipment_id
    ,A41.shipment_status_id
    ,A41.CHANGE_DATE
FROM (
    SELECT DISTINCT shipment_id
        ,holder_employee_id
        ,shipment_status_id
        ,CHANGE_DATE
        ,row_number() OVER (
            PARTITION BY shipment_id ORDER BY CHANGE_DATE ASC
            ) AS rn1
    FROM db1.table1
    WHERE TRUNC(CHANGE_DATE) = to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD')
    ) A41
WHERE rn1 = CASE 
        WHEN shipment_status_id IN (32)
            AND rn1 = 1
            THEN 2
        WHEN shipment_status_id IN (32)
            AND rn1 = 2
            THEN 3
        WHEN shipment_status_id IN (32)
            AND rn1 = 3
            THEN 4
        WHEN shipment_status_id IN (32)
            AND rn1 = 4
            THEN 5
        WHEN shipment_status_id IN (32)
            AND rn1 = 5
            THEN 6
        WHEN shipment_status_id IN (32)
            AND rn1 = 6
            THEN 7
        WHEN shipment_status_id IN (32)
            AND rn1 = 7
            THEN 8
        WHEN shipment_status_id IN (32)
            AND rn1 = 8
            THEN 9
        WHEN shipment_status_id IN (32)
            AND rn1 = 9
            THEN 10
        END

Upvotes: 0

Views: 91

Answers (2)

Vijayakumar Udupa
Vijayakumar Udupa

Reputation: 1145

You can use LEAD() function for this case.

Select shipment_id,
       status
       lead(status,1) over(partition by shipment_id order by change_date) next_status,
       change_date,
       lead(change_date,1) over (partition by shipment_id order by change_date) next_change_date
from table 1
 where shipment_id='1234'
 order by change_date;

Note: For last row 'LEAD() function output will be null. You can specify 3rd argument to substitute null outputs. ex: LEAD(status,1,status) which gives status of last row it self instead of null

Upvotes: 1

Boneist
Boneist

Reputation: 23578

Looks like LEAD() is exactly what you're after:

WITH table1 AS (SELECT 1234 shipment_id, 211 status, to_date('10/01/2017 12:32', 'dd/mm/yyyy hh24:mi') change_date, 1111 employee_id FROM dual UNION ALL
                SELECT 1234 shipment_id, 32 status, to_date('10/01/2017 12:32', 'dd/mm/yyyy hh24:mi') change_date, 1111 employee_id FROM dual UNION ALL
                SELECT 1234 shipment_id, 23 status, to_date('10/01/2017 12:32', 'dd/mm/yyyy hh24:mi') change_date, 1111 employee_id FROM dual)
-- end of mimicking a table called table1 with data in it. See SQL below:
SELECT shipment_id,
       status,
       change_date,
       employee_id,
       LEAD(status) OVER (PARTITION BY shipment_id ORDER BY change_date) next_status,
       LEAD(change_date) OVER (PARTITION BY shipment_id ORDER BY change_date) next_change_date,
       LEAD(employee_id) OVER (PARTITION BY shipment_id ORDER BY change_date) next_employee_id
FROM   table1;

SHIPMENT_ID     STATUS CHANGE_DATE EMPLOYEE_ID NEXT_STATUS NEXT_CHANGE_DATE NEXT_EMPLOYEE_ID
----------- ---------- ----------- ----------- ----------- ---------------- ----------------
       1234        211 10/01/2017         1111          23 10/01/2017 12:32             1111
       1234         23 10/01/2017         1111          32 10/01/2017 12:32             1111
       1234         32 10/01/2017         1111

N.B. If you want to then filter your rows to return the results just for a specific row, you will need to use an outer query to do that, because analytic functions work over the current result set, and if you filter too early you'll end up with the wrong results.

Upvotes: 2

Related Questions