Multiple condition in one case statement using oracle

user_2907032 0
Viewed 113

I have following sql and result:

SELECT oh.order_id,disallow_short_ship,sm.number
FROM table1 oh
    LEFT JOIN table2 sm
        ON sm.client_id = oh.client_id
            AND sm.site_id = oh.from_site_id
            AND sm.order_id = oh.order_id
WHERE oh.order_id = 'abc'
GROUP BY oh.order_id,oh.disallow_short_ship,sm.number
ORDER BY oh.order_id desc;

Result:

order_id   disallow_short_ship      number 
abc        null                     2107
abc        null                     2108

The expected result is to put case condition if the order have more that one result then compare both the number column, the greater number(2108) should have disallow_short_ship = 'Y' and for small number (2107) disallow_short_ship = 'N' as result.

I have tried to write case statement for disallow_short_ship column, but do I need to put two condition within one case statement.

Expected Result:

order_id   disallow_short_ship   number 
abc        N                     2107
abc        Y                     2108
oracle sql
1 Answers

Use window functions and a case expression:

SELECT oh.order_id, disallow_short_ship, sm.number,
       (CASE WHEN COUNT(*) OVER (PARTITION BY oh.order_id) > 1 AND
                  ROW_NUMBER() OVER (PARTITION BY oh.order_id ORDER BY number) = 1
             THEN 'N'
             WHEN COUNT(*) OVER (PARTITION BY oh.order_id) > 1 AND
                  ROW_NUMBER() OVER (PARTITION BY oh.order_id ORDER BY number DESC) = 1
             THEN 'Y'
             ELSE disallow_short_ship
        END) as disallow_short_ship             
FROM table1 oh LEFT JOIN
     table2 sm
     ON sm.client_id = oh.client_id AND
        sm.site_id = oh.from_site_id AND
        sm.order_id = oh.order_id
WHERE oh.order_id = 'abc'
GROUP BY oh.order_id, oh.disallow_short_ship, sm.number
ORDER BY oh.order_id desc;
user_11440350
Related