union et group by

**aurelie83** · 13/09/2007, 17h44

Voici une longue requete :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
(select DISTINCT
 CS.SAL_MATR as MATRICULE
,S.SAL_NOSS
,(TO_DATE('30/12/1899', 'DD/MM/YYYY') + CS.POT_DU + 2) as appo
, MAX(CS.POT_VAL) as appo_val
, decode(CAT_ID,'C',CS.POT_VAL*13) as REMU_THEO_CADRE_13
, decode(CAT_ID,'E',CS.POT_VAL*13) as REMU_THEO_ETAM_13
, '31/12/' || (to_char(sysdate,'yyyy')) as TEMPS_ID
FROM CONST_SAL CS, SALARIE S
WHERE  CS.ARC_ID ='APPO'
and s.SAL_MATR=CS.SAL_MATR
 
and CS.MON_ID = 'EUR' 
and (CS.POT_DU) = (select max(APPO.POT_DU)
			FROM CONST_SAL APPO
			WHERE  APPO.ARC_ID ='APPO'
			and APPO.MON_ID = 'EUR' 
			and APPO.SAL_MATR=CS.SAL_MATR
			and (TO_DATE('30/12/1899', 'DD/MM/YYYY') + APPO.POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')),'DD/MM/YYYY')
			)
AND CS.SAL_MATR not in(SELECT SAL_MATR 
			FROM CONST_SAL 
			WHERE ARC_ID ='OBAN'
			AND (TO_DATE('30/12/1899', 'DD/MM/YYYY') + POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')),'DD/MM/YYYY')
			) 
AND CS.SAL_MATR NOT IN (SELECT SAL_MATR 
			FROM CCMX.H_ES_SAUV HS 
			WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + HS.E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')))
			AND HS.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
			UNION 
				(SELECT SAL_MATR 
				FROM CCMX.H_ES H 
				WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')))
				AND H.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
 
				))
)
UNION 
(select DISTINCT
 CS.SAL_MATR as MATRICULE
,S.SAL_NOSS
,(TO_DATE('30/12/1899', 'DD/MM/YYYY') + CS.POT_DU + 2) as appo
, MAX(CS.POT_VAL) as appo_val
, decode(CAT_ID,'C',CS.POT_VAL*13) as REMU_THEO_CADRE_13
, decode(CAT_ID,'E',CS.POT_VAL*13) as REMU_THEO_ETAM_13
, '31/12/' || (to_char(sysdate,'yyyy')-1) as TEMPS_ID
FROM CONST_SAL CS, SALARIE S
WHERE  CS.ARC_ID ='APPO'
and s.SAL_MATR=CS.SAL_MATR
 
and CS.MON_ID = 'EUR' 
and (CS.POT_DU) = (select max(APPO.POT_DU)
			FROM CONST_SAL APPO
			WHERE  APPO.ARC_ID ='APPO'
			and APPO.MON_ID = 'EUR' 
			and APPO.SAL_MATR=CS.SAL_MATR
			and (TO_DATE('30/12/1899', 'DD/MM/YYYY') + APPO.POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')-1),'DD/MM/YYYY')
			)
AND CS.SAL_MATR not in(SELECT SAL_MATR 
			FROM CONST_SAL 
			WHERE ARC_ID ='OBAN'
			AND (TO_DATE('30/12/1899', 'DD/MM/YYYY') + POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')-1),'DD/MM/YYYY')
			) 
AND CS.SAL_MATR NOT IN (SELECT SAL_MATR 
			FROM CCMX.H_ES_SAUV HS 
			WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + HS.E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')-1))
			AND HS.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
			UNION 
				(SELECT SAL_MATR 
				FROM CCMX.H_ES H 
				WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')-1))
				AND H.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
				))
)
 
UNION
select DISTINCT
 CS.SAL_MATR as MATRICULE
,S.SAL_NOSS
,(TO_DATE('30/12/1899', 'DD/MM/YYYY') + CS.POT_DU + 2) as appo
, MAX(CS.POT_VAL) as appo_val
, decode(CAT_ID,'C',CS.POT_VAL*13) as REMU_THEO_CADRE_13
, decode(CAT_ID,'E',CS.POT_VAL*13) as REMU_THEO_ETAM_13
, '31/12/' || (to_char(sysdate,'yyyy')-2) as TEMPS_ID
FROM CONST_SAL CS, SALARIE S
WHERE  CS.ARC_ID ='APPO'
and s.SAL_MATR=CS.SAL_MATR
 
and CS.MON_ID = 'EUR' 
and (CS.POT_DU) = (select max(APPO.POT_DU)
			FROM CONST_SAL APPO
			WHERE  APPO.ARC_ID ='APPO'
			and APPO.MON_ID = 'EUR' 
			and APPO.SAL_MATR=CS.SAL_MATR
			and (TO_DATE('30/12/1899', 'DD/MM/YYYY') + APPO.POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')-2),'DD/MM/YYYY')
			)
AND CS.SAL_MATR not in(SELECT SAL_MATR 
			FROM CONST_SAL 
			WHERE ARC_ID ='OBAN'
			AND (TO_DATE('30/12/1899', 'DD/MM/YYYY') + POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')-2),'DD/MM/YYYY')
			) 
AND CS.SAL_MATR NOT IN (SELECT SAL_MATR 
			FROM CCMX.H_ES_SAUV HS 
			WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + HS.E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')-2))
			AND HS.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
			UNION 
				(SELECT SAL_MATR 
				FROM CCMX.H_ES H 
				WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')-2))
				AND H.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
				))
 
GROUP BY 
CS.SAL_MATR 
,S.SAL_NOSS
,(TO_DATE('30/12/1899', 'DD/MM/YYYY') + CS.POT_DU + 2) 
,decode(CAT_ID,'C',CS.POT_VAL*13) 
, decode(CAT_ID,'E',CS.POT_VAL*13)

Cette requete me renvoie l'erreur :
ORA-00937: la fonction de groupe ne porte pas sur un groupe simple
ou ORA-00933: SQL command not properly ended.

Le "group by" fonctionne pour chaque partie séparement...mais pas des que je rajoute "union".

une idée?

**aurelie83** · 13/09/2007, 18h00

C'est bon, j'ai resolu mon pb...
j'ai mis un group by apres chaque select...
et apparemment ca passe.

union et group by

SQL Oracle

Discussions similaires

Partager

Partager