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 |
