1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| WITH ta AS
(SELECT 1 AS KEY, 'X2' AS VALUE, 'E' AS TYPE FROM DUAL UNION ALL
SELECT 1 AS KEY, 'X3' AS VALUE, 'E' AS TYPE FROM DUAL UNION ALL
SELECT 2, 'X3', 'D' FROM DUAL UNION ALL
SELECT 3, 'X4', 'D' FROM DUAL),
tb AS
(SELECT 'X2' AS VALUE FROM DUAL UNION ALL
SELECT 'X6' FROM DUAL UNION ALL
SELECT 'V32' FROM DUAL UNION ALL
SELECT 'V45' FROM DUAL)
SELECT ta.KEY, tb.VALUE, ta.TYPE, 'OK'
FROM ta, tb
WHERE ta.KEY = 1 AND ta.VALUE = tb.VALUE
UNION ALL
SELECT KEY, VALUE, TYPE, 'NOK'
FROM (SELECT ta.KEY, tb.VALUE, ta.TYPE
FROM ta, tb
WHERE ta.KEY = 1 AND ta.VALUE <> tb.VALUE
MINUS
SELECT ta.KEY, tb.VALUE, ta.TYPE
FROM ta, tb
WHERE ta.KEY = 1 AND ta.VALUE = tb.VALUE);
-- RESULTAT --
KEY VAL T OK/NOK
----- --- - ---
1 X2 E OK
1 V32 E NOK
1 V45 E NOK
1 X6 E NOK |
Partager