1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| WITH ROL AS (SELECT '00001' NO, '09087' indice FROM dual
UNION ALL SELECT '00001', '9832' FROM dual
UNION ALL SELECT '00001', '' FROM dual
UNION ALL SELECT '00002', '1234' FROM dual
UNION ALL SELECT '00002', '' FROM dual
UNION ALL SELECT '00003', '' FROM dual
UNION ALL SELECT '00003', '' FROM dual),
LIGNE AS (SELECT '09087' indice, '01' ID FROM dual
UNION ALL SELECT '1234', '02' FROM dual
UNION ALL SELECT '9832', '03' FROM dual),
AUT AS (SELECT '01' ID, 100 secteur FROM dual
UNION ALL SELECT '02', 200 FROM dual
UNION ALL SELECT '03', 300 FROM dual)
SELECT rol.no, MAX(secteur) KEEP (DENSE_RANK FIRST ORDER BY rol.indice) secteur
FROM ROL, AUT, LIGNE
WHERE ligne.indice(+) = rol.indice
AND aut.id(+) = ligne.id
GROUP BY rol.no |
Partager