ORACLE - Récupération champ sous requête

Bonjour à tous,

Ce n'est pas faute d'avoir chercher ... Mais je m'avoue vaincu. ;)

Mon problème simplifié :
Sur une table T, je cherche à retourner des valeurs doublonnées.
Les critères de doublon sont une concaténation de champs : C1 || C2 || C3
La table est alimenté quotidiennement par des lots : L1, L2, L3, L4 ...

Je cherche à trouver, sur mon dernier lot (Je le connais chaque jour, prenons ici L4), les doublons pouvant être :

• sur le lot quotidien
  sur un lot antérieur

Ma requête ci-dessous semble fonctionner correctement, mais j'aimerais récupérer, dans le select principal, le numéro de lot sur lequel se trouve le doublon.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SELECT (CRE_ENR_K.NUM_CONTR || CRE_ENR_K.EVC_DT_TRAIT_EVT || CRE_ENR_K.CD_OPERATION || CRE_ENR_K.CD_ETAT_TRANS || CRE_ENR_K.CD_ETAT_ACT_GEST || CRE_ENR_K.NATURE_CRO) as CC FROM CRE_ENR_K WHERE CRE_ENR_K.CD_PRODUIT in ('127','135','138','180','320','329','335','402','403','405','408','418','502','518','523','543','544','617','645','646','718','766','804','852','856','858','859','872','895','896','916','921','925','926','940','941','943','949','954','984') AND CRE_ENR_K.NUM_LOT = '01225' and (CRE_ENR_K.NUM_CONTR || CRE_ENR_K.EVC_DT_TRAIT_EVT || CRE_ENR_K.CD_OPERATION || CRE_ENR_K.CD_ETAT_TRANS || CRE_ENR_K.CD_ETAT_ACT_GEST || CRE_ENR_K.NATURE_CRO) IN (SELECT (K2.NUM_CONTR || K2.EVC_DT_TRAIT_EVT || K2.CD_OPERATION || K2.CD_ETAT_TRANS || K2.CD_ETAT_ACT_GEST || K2.NATURE_CRO) FROM CRE_ENR_K K2 WHERE K2.CD_PRODUIT in ('127','135','138','180','320','329','335','402','403','405','408','418','502','518','523','543','544','617','645','646','718','766','804','852','856','858','859','872','895','896','916','921','925','926','940','941','943','949','954','984') AND K2.CD_OPERATION NOT IN ('21','30') AND K2.NUM_LOT >= '01210' GROUP BY (K2.NUM_CONTR || K2.EVC_DT_TRAIT_EVT || K2.CD_OPERATION || K2.CD_ETAT_TRANS || K2.CD_ETAT_ACT_GEST || K2.NATURE_CRO) HAVING count((K2.NUM_CONTR || K2.EVC_DT_TRAIT_EVT || K2.CD_OPERATION || K2.CD_ETAT_TRANS || K2.CD_ETAT_ACT_GEST || K2.NATURE_CRO)) > 1 )

---

Quelqu'un aurait'il une idée ?

Bonsoir,

Code:

---

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

SELECT * FROM CRE_ENR_K KExt WHERE CRE_ENR_K.NUM_CONTR || CRE_ENR_K.EVC_DT_TRAIT_EVT || CRE_ENR_K.CD_OPERATION || CRE_ENR_K.CD_ETAT_TRANS || CRE_ENR_K.CD_ETAT_ACT_GEST || CRE_ENR_K.NATURE_CRO) IN (/*tarequete*/ SELECT (CRE_ENR_K.NUM_CONTR || CRE_ENR_K.EVC_DT_TRAIT_EVT || CRE_ENR_K.CD_OPERATION || CRE_ENR_K.CD_ETAT_TRANS || CRE_ENR_K.CD_ETAT_ACT_GEST || CRE_ENR_K.NATURE_CRO) AS CC FROM CRE_ENR_K WHERE CRE_ENR_K.CD_PRODUIT IN ('127','135','138','180','320','329','335','402','403','405','408','418','502','518','523','543','544','617','645','646','718','766','804','852','856','858','859','872','895','896','916','921','925','926','940','941','943','949','954','984') AND CRE_ENR_K.NUM_LOT = '01225' AND (CRE_ENR_K.NUM_CONTR || CRE_ENR_K.EVC_DT_TRAIT_EVT || CRE_ENR_K.CD_OPERATION || CRE_ENR_K.CD_ETAT_TRANS || CRE_ENR_K.CD_ETAT_ACT_GEST || CRE_ENR_K.NATURE_CRO) IN (SELECT (K2.NUM_CONTR || K2.EVC_DT_TRAIT_EVT || K2.CD_OPERATION || K2.CD_ETAT_TRANS || K2.CD_ETAT_ACT_GEST || K2.NATURE_CRO) FROM CRE_ENR_K K2 WHERE K2.CD_PRODUIT IN ('127','135','138','180','320','329','335','402','403','405','408','418','502','518','523','543','544','617','645','646','718','766','804','852','856','858','859','872','895','896','916','921','925','926','940','941','943','949','954','984') AND K2.CD_OPERATION NOT IN ('21','30') AND K2.NUM_LOT >= '01210' GROUP BY (K2.NUM_CONTR || K2.EVC_DT_TRAIT_EVT || K2.CD_OPERATION || K2.CD_ETAT_TRANS || K2.CD_ETAT_ACT_GEST || K2.NATURE_CRO) HAVING count((K2.NUM_CONTR || K2.EVC_DT_TRAIT_EVT || K2.CD_OPERATION || K2.CD_ETAT_TRANS || K2.CD_ETAT_ACT_GEST || K2.NATURE_CRO)) > 1 ) )

---

pas tres elegant mais ca doit fonctionner. Désolé je ne peux pas tester

Cdt