Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
4 rows affected

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;
8 rows affected

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);


PRODUCT_NR
CUSTOMER
QUANTITY
PRICE


10023
X
12
250

10023
Y
10
120

10334
X
1
600

20765
Z
5
50