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
| WITH prerequete
AS (SELECT *
FROM TABLE1 table1
WHERE mp.commentaire not like '%Un texte'
AND table1.type = 0
AND NOT EXISTS
(SELECT NULL
FROM TABLE2 table2
WHERE table1.id = table2.id))
SELECT max(id) AS id
, identite
, datesaisie
, min(datedebut) AS datedebut
, DATE_SAISIE_FIN
, max(datefin) AS datefin
, AI
, max(puissance) keep(dense_rank first ORDER BY datefin DESC) AS puissance
, typesaisie
, idcause
, traitement
, commentaire
FROM prerequete p
where p.typesaisie in (1,3,4)
GROUP BY identite
, datesaisie
, typesaisie
, DATE_SAISIE_FIN
, AI
, idcause
, traitement
, commentaire
UNION
SELECT id AS id
, identite
, datesaisie
, datedebut AS datedebut
, DATE_SAISIE_FIN
, datefin AS datefin
, AI
, puissance AS puissance
, typesaisie
, idcause
, traitement
, commentaire
FROM prerequete p
where p.typesaisie not in (1,3,4)or p.typesaisie is null
ORDER BY dateSaisie DESC, dateDebut DESC |
Partager