SQL Oracle Add 'case when' if value in Join not available

user_16767382 0
Viewed 42

I have below two tables

enter image description here

enter image description here

~ What I am looking to do: I want to append the Price from Table 2 to Table 1. If the exact Quantity in Table 1 is not there in Table 2, take the closest max Quantity from Table 2 [i.e. 12 Quantity is not there in Table 2, the closest max is 20, so I want the price of that].

BUT in some cases there are no max Quantity values, which leads to some entries to fall out of my output. In this case, I am thinking to take the closest min Quantity from Table 2, to avoid some values to fall out of my output

So the result should look like:

enter image description here

I have tried below query but this does not account for taking the closest min Quantity when the closest max does not exist. Product_NR '20765' therefore falls out of my output. I'm thinking of 'case when' but not sure how to incorporate it below.

select 
Product_NR,
Customer,
Quantity,
min(price)

from
   ( select distinct
    t1.Product_NR,
    t1.Customer,
    t1.Quantity,
    t2.price,
    min(t2.quantity) over (partition by t1.product_NR) as Quantity_min
    
    from Table_1 t1
    left join Table_2 t2 on t1.Product_NR = t2.Product_NR
                    and t1.Quantity <= t2.Quantity

   )
where t2.Quantity = Quantity_min

group by
Product_NR,
Customer,
Quantity
oracle sql
1 Answers

From Oracle 12, you can use a LATERAL join and order the correlated sub-query to get the greater quantity rows before the lower quantities and then FETCH FIRST ROW ONLY:

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN LATERAL(
         SELECT price
         FROM   table2 t2
         WHERE  t1.product_nr = t2.product_nr
         ORDER BY
                CASE WHEN t1.quantity <= t2.quantity THEN t2.quantity END
                  ASC NULLS LAST,
                t2.quantity DESC
         FETCH FIRST ROW ONLY
       )
       ON (1 = 1);

Which, for the sample data:

CREATE TABLE table1 (product_nr, customer, quantity) AS
SELECT 10023, 'X', 12 FROM DUAL UNION ALL
SELECT 10023, 'Y', 10 FROM DUAL UNION ALL
SELECT 10334, 'X',  1 FROM DUAL UNION ALL
SELECT 20765, 'Z',  5 FROM DUAL;

CREATE TABLE table2 (product_nr, quantity, price) AS
SELECT 10023,   1, 100 FROM DUAL UNION ALL
SELECT 10023,  10, 120 FROM DUAL UNION ALL
SELECT 10023,  20, 250 FROM DUAL UNION ALL
SELECT 10023, 100, 400 FROM DUAL UNION ALL
SELECT 10334,   0, 250 FROM DUAL UNION ALL
SELECT 10334, 200, 600 FROM DUAL UNION ALL
SELECT 20765,   1,  40 FROM DUAL UNION ALL
SELECT 20765,   2,  50 FROM DUAL;

Outputs:

PRODUCT_NR CUSTOMER QUANTITY PRICE
10023 X 12 250
10023 Y 10 120
10334 X 1 600
20765 Z 5 50

db<>fiddle here

user_15092640
Related