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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
|
SELECT
t."VERSION_ID",
"CLIENT"."FAMILY" "CLIENT_FAMILY",
"CLIENT"."CODE" "CLIENT_CODE",
"CLIENT"."NAME" "CLIENT_NAME",
"CLIENT"."TYPE" "CLIENT_TYPE",
"PRODUIT"."NAME" "PRODUIT_NAME",
"PRODUIT"."FAMILY" "PRODUIT_FAMILY",
"PRODUIT"."DOMAIN" "PRODUIT_DOMAIN",
"PRODUIT"."CUSTOM" "PRODUIT_CUSTOM",
"PRODUIT"."TYPE" "PRODUIT_TYPE",
t."DATE" "VERSION_DATE",
t."NUMERO" "VERSION_NUMERO",
t."NEXT" "VERSION_NEXT",
"PRODUIT"."NORM" "PRODUIT_NORM",
"PRODUIT"."MODEL" "PRODUIT_MODEL",
"PRODUIT"."KEY" "PRODUIT_KEY",
Nvl((SELECT Count(*)
FROM external_service COUNT_EXS
WHERE COUNT_EXS."version_ref" = t."VERSION_ID"
),0) AS COUNT_EXS
t."LINK_SD",
t."IS_LAST",
t."LINK_SD",
t."IS_LAST",
t.COUNT_HISTORY
FROM
"PRODUIT"
JOIN
(SELECT
"VERSION"."PRODUIT_REF",
"VERSION"."VERSION_ID",
"VERSION"."DATE" ,
"VERSION"."NUMERO" ,
"VERSION"."NEXT" ,
"VERSION"."STATUS"
"LIEN_VERSION_CLIENT"."CLIENT_REF",
"LIEN_VERSION_CLIENT"."LINK_SD",
"LIEN_VERSION_CLIENT"."IS_LAST",
"LIEN_VERSION_CLIENT"."LINK_SD",
"LIEN_VERSION_CLIENT"."IS_LAST",
Count(*) Over (Partition By "VERSION"."PRODUIT_REF", "LIEN_VERSION_CLIENT"."CLIENT_REF") As COUNT_HISTORY
From "VERSION"
JOIN
"LIEN_VERSION_CLIENT"
ON "LIEN_VERSION_CLIENT"."VERSION_REF" = "VERSION"."VERSION_ID"
) t
ON t."PRODUIT_REF" = "PRODUIT"."PRODUIT_ID"
JOIN
"CLIENT"
ON "CLIENT"."CLIENT_ID" = "LIEN_VERSION_CLIENT"."CLIENT_REF"
WHERE t."STATUS" IN ('NEW', 'DIFFUSED')
AND "CLIENT"."SOURCE" = 'APPLICATION' |
Partager