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
|
SELECT
DEM_NO,
DATE_CREATION,
CASE WHEN DEM_ETAT_T IS NULL THEN DEM_ETAT
ELSE DEM_ETAT_T
END AS DEM_ETAT_FIN,
TI_NOM,
NOM_PRODUIT,
TITRE,
DATE_RESOLUTION,
ID_DEMANDE
ID_AFFECTATION,
MOTS_CLE,
ID_TIERS,
p1.ID_PRODUIT,
ID_AFF_T,
CASE WHEN ID_SOC_T IS NULL THEN ID_SOCIETE
ELSE ID_SOC_T
END AS ID_SOC_FIN
FROM DEMANDES p1
LEFT OUTER JOIN
(SELECT p1.ID_DEMANDE as dem, higher=MAX(p1.ID_TRAITEMENT)
FROM DEMANDE_TRAITEMENT p1
GROUP BY p1.ID_DEMANDE)AS ptrait
ON ptrait.dem = p1.ID_DEMANDE
LEFT OUTER JOIN
(SELECT ID_TRAITEMENT, ID_AFFECTATION AS ID_AFF_T, ID_SOCIETE AS ID_SOC_T, DEM_ETAT AS DEM_ETAT_T FROM DEMANDE_TRAITEMENT)AS pdemtout
ON pdemtout.ID_TRAITEMENT = ptrait.higher
LEFT OUTER JOIN
(SELECT TI_NOM, ID_TIERS AS tiers FROM N_TIERS ptiers ) AS ptiers
ON ptiers.tiers = p1.ID_TIERS
LEFT OUTER JOIN
(SELECT ID_PRODUIT, NOM_PRODUIT FROM N_PRODUIT pprod) AS pprod
ON pprod.ID_PRODUIT = p1.ID_PRODUIT
WHERE ID_SOCIETE = 3 |
Partager