Recherche d'une valeur d'une autre table

**joponta** · 04/10/2019, 11h14

C'est parfait.
Je vous remercie beaucoup pour vos contributions et je vous souhaite un bon week-end.

**joponta** · 04/10/2019, 12h02

Une dernière petite chose est-il possible d'avoir la liste de tous les présent en quatrième colonne.
J'ai vu qu'il était possible de le faire avec LIST AGG, mais je ne sais pas comment le mettre en place.

**JeanYvette** · 07/10/2019, 08h58

Bonjour

C'est potentiellement possible oui. La seule chose à voir est combien vous avez de présent, parce que au bout d'un moment, Oracle va crier parce que trop d'éléments.
De plus, il s'agit de TOUS les présents par jours ou doit-il y avoir des clauses en plus pour cela ?

**joponta** · 07/10/2019, 09h47

Bonjour,

ça représente un dizaine de nom par jour.
Merci pour votre aide

**JeanYvette** · 07/10/2019, 10h29

Je ne suis pas sûr que la méthode soit la plus belle mais voici un exemple de la syntaxe qu'il vous faut :

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
select  T1.dat
    ,   round(sum(case 
                    when T1.MOTIF   = 'PRESENT' 
                        and T1.HRAFIN >= '480' 
                        and T1.HRADEB <= '1200'  
                    then 1
                    else 0
                    end
                )
            ,   0
            )   as  NBTOTAL
    ,   round(sum(case 
                    when T1.MOTIF   = 'PRESENT' 
                        and T1.HRAFIN >= '480' 
                        and T1.HRADEB <= '1200'  
                        and T4.MATRI  is not null
                    then 1
                    else 0
                    end
                )
            ,   0
            )   as  NBCOND,   
      round(sum(case 
                    when T1.MOTIF   = 'PRESENT' 
                        and T1.HRAFIN >= '480' 
                        and T1.HRADEB <= '1200'  
                        and T4Passage.MATRI  is not null
                    then 1
                    else 0
                    end
                )
            ,   0
          )   as  NBPass,
          Presents.ListePresent
from    T1
    left join
        (   select  distinct
                    MATRI
            from    T4
            where   CRITERE  = 'CONDUCTEUR'
        )   T4
on  T4.MATRI    = T1.MATRI
left join
        (   select  distinct
                    MATRI
            from    T4
            where   CRITERE  = 'PASSAGER'
        )   T4Passager
        on  T4Passager.MATRI    = T1.MATRI
left join
      (
        select LISTAGG(MATRI, '; ') WITHIN GROUP (ORDER BY MATRI) as ListePresent, DAT
        from T1
        group by T1.DAT
      ) Presents
      on T1.DAT = Presents.DAT
where   T1.DAT = '2019-09-25'  
group by T1.DAT
;

Je me permet un petit ps : Si vous voulez rajouter des colonnes, pas de soucis. Que vous ayez potentiellement besoin de nous, pas de soucis. Là vous avez trouvé comment faire une liste en SQL sous Oracle, c'est très bien. Mais dans ce cas, pourquoi ne pas avoir tenté de l'implémenter dans votre requête et, si cela ne fonctionne pas, nous envoyer votre requête avec le problème ?

**joponta** · 07/10/2019, 11h55

Bonjour,

Voici ma requête. Lorsque je mets en place le left join avec LISTAGG, j'ai une erreur de groupe by au niveau de Presents.ListePresent

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
SELECT hophabs.dat,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
then (1200- 480)/60/12
else 0
end),0) as JOUR,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLPEMCRI.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0) as NBPLG3,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG2.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0)-
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLPEMCRI.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0)
as NBPLG2,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG1.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0)-
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG2.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0) 
as NBPLG1,
Presents.listePresent
from HOPHABS
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG3') AND PLPEMCRI.DATFIN = '31/12/2099'
) 
PLPEMCRI on PLPEMCRI.MATRI = HOPHABS.MATRI
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG2') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG2
 on PLG2.MATRI = HOPHABS.MATRI
 LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG1') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG1
 on PLG1.MATRI = HOPHABS.MATRI
left join
      (
        select LISTAGG(hophabs.MATRI, '; ') WITHIN GROUP (ORDER BY hophabs.MATRI) as ListePresent,hophabs.DAT
        from hophabs
        group by hophabs.DAT
      ) Presents
      on hophabs.DAT = Presents.DAT 
WHERE  extract ( year from hophabs.dat) = '2019' and EXTRACT(MONTh from HOPHABS.DAT) = '10' 
 
GROUP BY HOPHABS.DAT
ORDER BY HOPHABS.DAT

**JeanYvette** · 07/10/2019, 13h53

Il nous faudrait le code et le motif de l'erreur pour pouvoir aider au mieux

**joponta** · 07/10/2019, 14h33

ORA-00979: n'est pas une expression GROUP BY
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Erreur à la ligne 51, colonne 1

**JeanYvette** · 07/10/2019, 15h09

Mea culpa, je n'avais pas fait attention à votre version d'Oracle. La clause LISTAGG n'existe qu'à partir de la version 11g, vous ne l'avez donc pas.
Il vous faut donc, pour cela, passer par la clause "sys_connect_by_path"

Dans ce cas, ce qu'il pourrait être fait. Dans la jointure où j'avais initialement mis le "listagg", ne faites qu'un select des matricules / date.
Par exemple :

Code :

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

1
2
3
4
 
SELECT matri,DAT,row_number() over (partition by DAT order by matri) as rn
FROM T1 
WHERE MOTIF = 'PRESENT'

En gardant bien la même clause 'ON' sur le 'JOIN'.

Ensuite, au niveau de votre select principal, remplacez le "Presents.listePresent" par ceci :

Code :

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

ltrim(sys_connect_by_path(Presents.MATRI,','),',') as ListePresent

et dans votre clause Where, rajoutez du code comme ceci :

Code :

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

1
2
3
4
5
 
where connect_by_isleaf = 1
    connect by Presents.DAT= prior Presents.DAT
           and rn = prior rn+1
 start with rn = 1

Ensuite voir avec les différents paramètres de la clause "sys_connect_by_path"

Je vous invite à faire des tests et revenir vers nous si besoin avec la requête ET le code erreur

**tbc92** · 07/10/2019, 15h18

Un petit peu de culture ou de logique sur SQL serait utile.
Quand tu fais SELECT ... from ... GROUP BY ... , ça veut dire quoi ?

Dans la 1ère partie du SELECT (les colonnes que tu veux aficher), il y a des colonnes du type n° de matricule, n° de période, et des colonnes calculées (des SUM, des AVG, éventuellement des MIN ou des MAX ou des COUNT ...)
Et dans le GROUP BY, il faut retrouver toutes les colonnes de type identifiant.

Toi tu fais : SELECT hophabs.dat , Presents.listePresent, + des colonnes de type SUMDonc dans le group by, il faut GROUP BY hophabs.dat , Presents.listePresent .

Dans le group by , tu peux éventuellement avoir plus de colonnes que ces 2 colonnes hophabs.dat et Presents.listePresent ; c'est un peu bizarre, c'est très rare, mais pourquoi pas. Mais tu ne peux pas avoir :

Code :

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

1
2
3
SELECT hophabs.dat ,  Presents.listePresent,  + des colonnes de type SUM  
FROM ...
GROUP BY   hophabs.dat

Oracle serait bien incapable de deviner quoi afficher dans la colonne liste_present.

Dans ton cas, j'imagine que pour une date donnée, la valeur de Liste_presents est fixe. C'est fréquent. Toi tu sais que cette valeur est toujours la même pour une date donnée, et donc ça ne change pas grnad chose si on la met dans le GROUP BY.
C'est vrai. Mais il faut quand même adapter la requête :

Code :

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

1
2
3
SELECT hophabs.dat ,  Presents.listePresent,  + des colonnes de type SUM  
FROM ...
GROUP BY   hophabs.dat   ,  Presents.listePresent

Ou bien , tupeux aussi faire :

Code :

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

1
2
3
SELECT hophabs.dat ,  min ( Presents.listePresent )  ,  + des colonnes de type SUM  
FROM ...
GROUP BY   hophabs.dat

Suivant le contenu de tes tables, et ton besoin, tu peux vouloir la requête 1, ou bien la requête 2, ou bien tu es dans un cas où requête 1 et requête 2 sont strictement équivalentes.

Il me semble que certains moteurs SQL acceptent qu'on oublie certains noms de colonnes dans le Group BY, et ils le rajoutent d'eux même. Mais Oracle est strict sur ce point là, c'est une des qualités d'Oracle.

**joponta** · 07/10/2019, 15h33

j'ai essayé avec sys connect et en mettant Presents.ListePresent dans le group by, mais sans réussite :

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
SELECT hophabs.dat,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
then (1200- 480)/60/12
else 0
end),0) as SPPJ,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLPEMCRI.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0) as NBPLG3,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG2.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0)-
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLPEMCRI.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0)
as NBPLG2,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG1.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0)-
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG2.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0) 
as NBPLG1,
ltrim(sys_connect_by_path(Presents.MATRI,','),',') as ListePresent
from HOPHABS
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG3') AND PLPEMCRI.DATFIN = '31/12/2099'
) 
PLPEMCRI on PLPEMCRI.MATRI = HOPHABS.MATRI
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG2') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG2
 on PLG2.MATRI = HOPHABS.MATRI
 LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG1') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG1
 on PLG1.MATRI = HOPHABS.MATRI
left join
      (
        SELECT hophabs.matri,hophabs.DAT,row_number() over (partition by hophabs.DAT order by hophabs.matri) as rn
FROM hophabs
WHERE HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')
 
      ) Presents
      on hophabs.DAT = Presents.DAT 
WHERE  extract ( year from hophabs.dat) = '2019' and EXTRACT(MONTh from HOPHABS.DAT) = '10' 
AND connect_by_isleaf = 1
    connect by Presents.DAT= prior Presents.DAT
           and rn = prior rn+1
 start with rn = 1
GROUP BY HOPHABS.DAT,Presents.ListePresent
ORDER BY HOPHABS.DAT

**JeanYvette** · 07/10/2019, 15h35

Je rejoins tes dires tbc92, désolé de mon manque de rigueur le but, pour moi, étant de donnée une idée syntaxique sur comment arrivé au but voulu et non la requête exacte ne pouvant testé.

Toutefois, joponta, nous n'avons toujours pas de code erreur ou de descriptif du problème qui fait que ca ne va pas... Il est donc difficile de pouvoir aider comme il le faut

**joponta** · 07/10/2019, 15h39

dans la requête j'ai le code ci-dessous :

ORA-00904: "PRESENTS"."LISTEPRESENT" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 86, colonne 22

**JeanYvette** · 07/10/2019, 15h46

Normal "Presents.listePresent" n'existe pas. ListePresent est un alias de votre requête principale, plus de votre sous-requête "Presents"..

**joponta** · 07/10/2019, 15h59

Si je mets ListePresent dans le group by, j'ai le même type d'erreur :
ORA-00904: "LISTEPRESENT" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 86, colonne 22

**escartefigue** · 07/10/2019, 16h17

Essayez en remplaçant l'alias par sa formule, à savoir ltrim(sys_connect_by_path(Presents.MATRI,','),',').

Et, comme déjà mentionné page précédente, remplacez la formule then (1200- 480)/60/12 par then 1.

Note : concernant les incohérences entre colonnes du SELECT et du GROUP BY, peut-être venez-vous du monde MYSQL très laxiste sur ce chapitre, si vous y avez pris de mauvaises habitudes, sachez que c'est le seul SGBD qui autorise ce genre d'entorses à la règle

**joponta** · 07/10/2019, 16h38

J'ai toujours une erreur, différente cette fois :
ORA-30002: La fonction SYS_CONNECT_BY_PATH est interdite ici
30002. 00000 - "SYS_CONNECT_BY_PATH function is not allowed here"
*Cause: SYS_CONNECT_BY_PATH function was called at places other than the
SELECT list and ORDER BY clause.
Erreur à la ligne 86, colonne 28

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
SELECT hophabs.dat,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
then 1
else 0
end),0) as SPPJ,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLPEMCRI.MATRI is not null
then 1
else 0
end),0) as NBPLG3,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG2.MATRI is not null
then 1
else 0
end),0)-
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') 
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLPEMCRI.MATRI is not null
then 1
else 0
end),0)
as NBPLG2,
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG1.MATRI is not null
then (1200- 480)/60/12 
else 0
end),0)-
round(sum (
CASE 
WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  
and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200'
AND PLG2.MATRI is not null
then 1
else 0
end),0) 
as NBPLG1,
ltrim(sys_connect_by_path(Presents.MATRI,','),',') as ListePresent
from HOPHABS
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG3') AND PLPEMCRI.DATFIN = '31/12/2099'
) 
PLPEMCRI on PLPEMCRI.MATRI = HOPHABS.MATRI
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG2') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG2
 on PLG2.MATRI = HOPHABS.MATRI
 LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG1') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG1
 on PLG1.MATRI = HOPHABS.MATRI
left join
      (
        SELECT hophabs.matri,hophabs.DAT,row_number() over (partition by hophabs.DAT order by hophabs.matri) as rn
FROM hophabs
WHERE HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')
 
      ) Presents
      on hophabs.DAT = Presents.DAT 
WHERE  extract ( year from hophabs.dat) = '2019' and EXTRACT(MONTh from HOPHABS.DAT) = '10' 
AND connect_by_isleaf = 1
    connect by Presents.DAT= prior Presents.DAT
           and rn = prior rn+1
 start with rn = 1
GROUP BY HOPHABS.DAT,ltrim(sys_connect_by_path(Presents.MATRI,','),',')
ORDER BY HOPHABS.DAT

**JeanYvette** · 08/10/2019, 09h51

Bonjour,

Dans ce cas, pourriez-vous tenter cela ?

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
SELECT hophabs.dat,
round(sum ( CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200' then 1 else 0 end),0) as SPPJ,
round(sum ( CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200' AND PLPEMCRI.MATRI is not null then 1 else 0 end),0) as NBPLG3,
round(sum ( CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200' AND PLG2.MATRI is not null then 1 else 0 end),0)-
	round(sum(CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') and HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200' AND PLPEMCRI.MATRI is not null then 1 else 0 end),0) as NBPLG2,
round(sum(CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') and  HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200' AND PLG1.MATRI is not null then 1 else 0 end),0)-
	round(sum(CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') and HOPHABS.HRAFIN >= '480' AND HOPHABS.HRADEB <= '1200' AND PLG2.MATRI is not null then 1 else 0 end),0) as NBPLG1,
rtrim(xmlagg( xmlelement(e, Presents.MATRI, ',') order by Presents.MATRI ).extract('//text()').getclobval(), ',') as ListePresent
from HOPHABS
	LEFT JOIN 
		( SELECT DISTINCT PLPEMCRI.MATRI
			FROM PLPEMCRI
			WHERE PLPEMCRI.CODECRIT in ('PLG3') AND PLPEMCRI.DATFIN = '31/12/2099'
		) PLPEMCRI 
		on PLPEMCRI.MATRI = HOPHABS.MATRI
	LEFT JOIN 
		( SELECT DISTINCT PLPEMCRI.MATRI
			FROM PLPEMCRI
			WHERE PLPEMCRI.CODECRIT in ('PLG2') AND PLPEMCRI.DATFIN = '31/12/2099'
		) PLG2
		on PLG2.MATRI = HOPHABS.MATRI
	LEFT JOIN 
		( SELECT DISTINCT PLPEMCRI.MATRI
			FROM PLPEMCRI
			WHERE PLPEMCRI.CODECRIT in ('PLG1') AND PLPEMCRI.DATFIN = '31/12/2099'
		) PLG1
		on PLG1.MATRI = HOPHABS.MATRI
	left join
      (
        SELECT hophabs.matri,hophabs.DAT,row_number() over (partition by hophabs.DAT order by hophabs.matri) as rn
		FROM hophabs
		WHERE HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')
      ) Presents
       on hophabs.DAT = Presents.DAT 
WHERE  extract ( year from hophabs.dat) = '2019' and EXTRACT(MONTh from HOPHABS.DAT) = '10' 
GROUP BY HOPHABS.DAT
ORDER BY HOPHABS.DAT

**joponta** · 08/10/2019, 11h57

Bonjour,

Avec cette requête, je n'ai plus d'erreur.
Par contre les chiffre des 4 premières colonnes sont erronés, et l'affichage de la liste des présent répète le même matricule.

**joponta** · 09/10/2019, 11h53

Bonjour,

J'ai réussi avec LISTAGG à afficher les PRESENTS avec les motifs :

Code :

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

HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA',  'WBCG12NP',  'WBCG24NP')

Est-il possible d'avoir uniquement le nom des présents qui font parti du nombre PLG3N, PLG2N, et PLG1N ?

Voici la requête :

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
SELECT hophabs.dat,
round(sum ( CASE  WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') then 1 else 0 end),0) as SPPJ,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') AND PLPEMCRI.MATRI is not null then 1 else 0 end),0) as NBPLG3,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  AND PLG2.MATRI is not null then 1 else 0 end),0)-
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') AND PLPEMCRI.MATRI is not null then 1else 0 end),0)as NBPLG2,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  AND PLG1.MATRI is not null then 1 else 0 end),0)-
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  AND PLG2.MATRI is not null then 1 else 0 end),0) as NBPLG1,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA',  'WBCGNPCETC','WBCG12NP',  'WBCG24NP') then 1 else 0 end),0) as SPPN,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG3N.MATRI is not null then 1 else 0end),0) as NBPLG3N,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG2N.MATRI is not null then 1 else 0 end),0)-
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG3N.MATRI is not null then 1 else 0 end),0)as NBPLG2N,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG1N.MATRI is not null then 1else 0end),0)-
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG2N.MATRI is not null then 1 else 0 end),0) as NBPLG1N,
Presents.ListePresent
from HOPHABS
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG3') AND PLPEMCRI.DATFIN = '31/12/2099'
) 
PLPEMCRI on PLPEMCRI.MATRI = HOPHABS.MATRI
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG2') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG2
 on PLG2.MATRI = HOPHABS.MATRI
 LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG1') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG1
 on PLG1.MATRI = HOPHABS.MATRI
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG3') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG3N
 on PLG3N.MATRI = HOPHABS.MATRI
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG2') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG2N
 on PLG2N.MATRI = HOPHABS.MATRI
 LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG1') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG1N
 on PLG1N.MATRI = HOPHABS.MATRI
 left join
      (
        select LISTAGG(hopempl.nompre, '; ') WITHIN GROUP (ORDER BY hophabs.MATRI) as ListePresent,hophabs.DAT
        from hophabs, hopempl
        WHERE HOPHABS.MATRI = HOPEMPL.MATRI and HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA',  'WBCG12NP',  'WBCG24NP') 
        group by hophabs.DAT
      ) Presents
      on hophabs.DAT = Presents.DAT
WHERE  extract ( year from hophabs.dat) = '2019' and EXTRACT(MONTh from HOPHABS.DAT) = '10' 
 
GROUP BY HOPHABS.DAT,Presents.ListePresent
ORDER BY HOPHABS.DAT

Recherche d'une valeur d'une autre table [10g]

SQL Oracle

Discussions similaires

Partager

Partager