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
| SELECT nome_arti_code_compose,
Lpad(' ',(level-1)*2)||nome_arti_code_compose v_compose_decale,
nome_ligne,
nome_arti_code_composant,
nome_coefficient,
nome_indice,
level,
nome_debut_validite,
nome_fin_validite
FROM nomenclature TNOM
WHERE nome_debut_validite <= trunc(sysdate)
AND NVL(nome_fin_validite, trunc(sysdate) ) >= trunc(sysdate)
and not exists
(
SELECT 'x'
FROM nomenclature
WHERE nome_debut_validite <= trunc(sysdate)
AND NVL(nome_fin_validite, trunc(sysdate) ) >= trunc(sysdate)
START WITH nome_arti_code_compose = TNOM.nome_arti_code_compose
CONNECT BY
PRIOR nome_arti_code_compose = nome_arti_code_composant
AND PRIOR nome_debut_validite <= trunc(sysdate)
AND NVL(PRIOR nome_fin_validite, trunc(sysdate)) >= trunc(sysdate)
)
START WITH nome_arti_code_composant = :composant
CONNECT BY
PRIOR nome_arti_code_compose = nome_arti_code_composant
AND PRIOR nome_debut_validite <= trunc(sysdate)
AND NVL(PRIOR nome_fin_validite, trunc(sysdate)) >= trunc(sysdate) |
Partager