Sous-requête avec LEFT JOIN

Version imprimable

Bonjour,
Dans la requête ci-dessous je récupère des noms avec la liste associée aux rôles de chaque nom.
Dans la partie left join avec sous requête, je souhaiterais également récupérer la liste des noms des responsables de chacun.
J'ai une erreur VALIDERU.MATRI identificateur non valide.
Merci pour votre aide

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH,
listagg(HOPROLE.UTIL || ' : ' || coalesce(HOPROLE.HORSECT, ' '), '; ')
        within group ( order by HOPROLE.UTIL ) ROL,
valideur.valideur
From HOPEMPL, HOPSECH, HOPCTRA, HOPROLE
LEFT JOIN ( 
select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur from
( select hopempl.nompre from hopempl ,sfphierd where hopempl.matri = sfphierd.matri ) x
GROUP By x.nompre
) valideur
on hopmepl.matri = valideur.matri
WHERE HOPEMPL.HORSECT = HOPSECH.HORSECT
AND HOPEMPL.MATRI = HOPCTRA.MATRI
AND SYSDATE Between HOPCTRA.DATDEB and HOPCTRA.DATFIN
AND HOPEMPL.SEITYPPOP not in ('V',' ', 'I')
AND HOPEMPL.MATRI not in ('989897','989898','98989826')
AND HOPEMPL.MATRI = HOPROLE.MATRI
 
GROUP BY (HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH, valideur.valideur)
ORDER BY HOPEMPL.HORSECT, HOPEMPL.SEITYPPOP

29/01/2020, 09h43
vanagreg

Bonjour,

C'est normal d'avoir cette erreur car tu n'as pas de colonne MATRI dans ta vue "valideur".
De toute façon ta requête est mal conçue, tu mélanges syntaxe Oracle "old style" et syntaxe ANSI.
Réécris la requête en utilisant uniquement la syntaxe ANSI.

Bonjour,

a priori il y a un GROUP BY inutile ici
Code:

1 2 3 4 5 6 7 LEFT JOIN (select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur from (select hopempl.nompre from hopempl, sfphierd where hopempl.matri = sfphierd.matri) x GROUP By x.nompre -- ICI ) valideur
De plus, pourquoi coder encore des jointures dans le WHERE, ça ne facilite pas la lecture :(

Merci pour vos retours mais je connais la méthode ANSI
J'ai ajouté x.matri dans la vue valideur, mais ça ne fonctionne pas

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH,
listagg(HOPROLE.UTIL || ' : ' || coalesce(HOPROLE.HORSECT, ' '), '; ')
        within group ( order by HOPROLE.UTIL ) ROL,
valideur.valideur
From HOPEMPL, HOPSECH, HOPCTRA, HOPROLE
LEFT JOIN ( 
select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur, x.matri from
( select hopempl.nompre from hopempl ,sfphierd where hopempl.matri = sfphierd.matri ) x
GROUP By x.nompre
) valideur
on hopmepl.matri = valideur.matri
WHERE HOPEMPL.HORSECT = HOPSECH.HORSECT
AND HOPEMPL.MATRI = HOPCTRA.MATRI
AND SYSDATE Between HOPCTRA.DATDEB and HOPCTRA.DATFIN
AND HOPEMPL.SEITYPPOP not in ('V',' ', 'I')
AND HOPEMPL.MATRI not in ('989897','989898','98989826')
AND HOPEMPL.MATRI = HOPROLE.MATRI
 
GROUP BY (HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH, valideur.valideur)
ORDER BY HOPEMPL.HORSECT, HOPEMPL.SEITYPPO

Tu ne peux pas avoir :

Code:

1
2
From HOPEMPL, HOPSECH, HOPCTRA, HOPROLE
LEFT JOIN (

Ce n'est pas ANSI. Il faut:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH,
listagg(HOPROLE.UTIL || ' : ' || coalesce(HOPROLE.HORSECT, ' '), '; ')
        within group ( order by HOPROLE.UTIL ) ROL,
valideur.valideur
From HOPEMPL
JOIN HOPSECH ON HOPEMPL.HORSECT = HOPSECH.HORSECT
JOIN HOPCTRA ON HOPEMPL.MATRI = HOPCTRA.MATRI
JOIN HOPROLE ON HOPEMPL.MATRI = HOPROLE.MATRI
LEFT JOIN ( 
select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur from
( select hopempl.nompre from hopempl ,sfphierd where hopempl.matri = sfphierd.matri ) x
GROUP By x.nompre
) valideur
on hopmepl.matri = valideur.matri
WHERE SYSDATE Between HOPCTRA.DATDEB and HOPCTRA.DATFIN
AND HOPEMPL.SEITYPPOP not in ('V',' ', 'I')
AND HOPEMPL.MATRI not in ('989897','989898','98989826');

J'ai toujours l'erreur VALIDEUR.MATRI identificateur non valide

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH,
listagg(HOPROLE.UTIL || ' : ' || coalesce(HOPROLE.HORSECT, ' '), '; ')
        within group ( order by HOPROLE.UTIL ) ROL,
valideur.valideur
From HOPEMPL
JOIN HOPSECH ON HOPEMPL.HORSECT = HOPSECH.HORSECT
JOIN HOPCTRA ON HOPEMPL.MATRI = HOPCTRA.MATRI and SYSDATE Between HOPCTRA.DATDEB and HOPCTRA.DATFIN
JOIN HOPROLE ON HOPEMPL.MATRI = HOPROLE.MATRI and SYSDATE Between HOPROLE.DATDEB and HOPROLE.DATFIN
LEFT JOIN ( 
select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur from
( select hopempl.nompre from hopempl ,sfphierd where hopempl.matri = sfphierd.matri ) x
GROUP By x.nompre
) valideur
on hopmepl.matri = valideur.matri
WHERE 
HOPEMPL.SEITYPPOP not in ('V',' ', 'I')
AND HOPEMPL.MATRI not in ('989897','989898','98989826')
GROUP BY (HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH)

29/01/2020, 10h30
vanagreg

Dans la requête que j'ai reprise il manque l'ajout de x.matri dans la vue valideur, tu peux l'ajouter.

J'ai la même erreur en ajoutant x.matri dans la vue

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH,
listagg(HOPROLE.UTIL || ' : ' || coalesce(HOPROLE.HORSECT, ' '), '; ')
        within group ( order by HOPROLE.UTIL ) ROL,
valideur.valideur
From HOPEMPL
JOIN HOPSECH ON HOPEMPL.HORSECT = HOPSECH.HORSECT
JOIN HOPCTRA ON HOPEMPL.MATRI = HOPCTRA.MATRI
JOIN HOPROLE ON HOPEMPL.MATRI = HOPROLE.MATRI
LEFT JOIN ( 
select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur, x.matri from
( select hopempl.nompre from hopempl ,sfphierd where hopempl.matri = sfphierd.matri ) x
GROUP By x.nompre
) valideur
on hopmepl.matri = valideur.matri
WHERE SYSDATE Between HOPCTRA.DATDEB and HOPCTRA.DATFIN
AND HOPEMPL.SEITYPPOP not in ('V',' ', 'I')
AND HOPEMPL.MATRI not in ('989897','989898','98989826');

29/01/2020, 10h49
vanagreg

Quelle est l'erreur exactement? Peux-tu exécuter la requête et mettre ici l'erreur complète?
29/01/2020, 10h55
joponta

Voici l'erreur :

Citation:

ORA-00904: "X"."MATRI" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 10, colonne 79
29/01/2020, 10h59
vanagreg

Dans la vue "x" tu ne sélectionnes pas la colonne matri... Assure-toi de bien sélectionner les colonnes dont tu as besoin dans tes sous-requêtes pour pouvoir les référencer.

J'ai ajouter hopempl.matri dans la vue x.
j'ai l'erreur ci-dessous :

Citation:

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

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH,
listagg(HOPROLE.UTIL || ' : ' || coalesce(HOPROLE.HORSECT, ' '), '; ')
        within group ( order by HOPROLE.UTIL ) ROL,
valideur.valideur
From HOPEMPL
JOIN HOPSECH ON HOPEMPL.HORSECT = HOPSECH.HORSECT
JOIN HOPCTRA ON HOPEMPL.MATRI = HOPCTRA.MATRI
JOIN HOPROLE ON HOPEMPL.MATRI = HOPROLE.MATRI
LEFT JOIN ( 
select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur, x.matri from
( select hopempl.nompre,hopempl.matri from hopempl ,sfphierd where hopempl.matri = sfphierd.matri ) x
GROUP By x.nompre
) valideur
on hopempl.matri = valideur.matri
WHERE SYSDATE Between HOPCTRA.DATDEB and HOPCTRA.DATFIN
AND HOPEMPL.SEITYPPOP not in ('V',' ', 'I')
AND HOPEMPL.MATRI not in ('989897','989898','98989826');

Ce serait bien de réfléchir un peu sur l'erreur retournée par Oracle avec la ligne concernée qui est la 10 en l'occurrence
Ne voyez-vous rien de choquant dans votre sous-requête :
Code:

1 2 3 select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur, x.matri from ( select hopempl.nompre,hopempl.matri from hopempl ,sfphierd where hopempl.matri = sfphierd.matri ) x GROUP By x.nompre

Il manque les colonnes dans un GROUP BY quand tu fais LISTAGG en version agrégée. De toute façon tu n'as pas besoin de ta sous-vue "valideur", tu peux écrire directement:

Code:

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
 
SELECT
    hopempl.seityppop,
    hopempl.matri,
    hopempl.nompre,
    hopempl.horsect,
    hopsech.libcourt,
    hopsech.libelle,
    hopempl.grouph,
    LISTAGG(hoprole.util || ' : ' || coalesce(hoprole.horsect, ' '), '; ') WITHIN GROUP(ORDER BY hoprole.util) rol,
    LISTAGG(hopempl.nompre, '; ') WITHIN GROUP(ORDER BY x.nompre) AS valideur
FROM
         hopempl
    JOIN hopsech ON hopempl.horsect = hopsech.horsect
    JOIN hopctra ON hopempl.matri = hopctra.matri
    JOIN hoprole ON hopempl.matri = hoprole.matri
    LEFT JOIN sfphierd ON hopempl.matri = sfphierd.matri 
WHERE sysdate BETWEEN hopctra.datdeb AND hopctra.datfin
   AND hopempl.seityppop NOT IN ('V', ' ', 'I')
   AND hopempl.matri NOT IN ('989897', '989898', '98989826')
GROUP BY hopempl.seityppop,
         hopempl.matri,
         hopempl.nompre,
         hopempl.horsect,
         hopsech.libcourt,
         hopsech.libelle,
         hopempl.grouph;

Merci pour vos contributions, le code ci-dessous fonctionne.

Code:

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
SELECT HOPEMPL.SEITYPPOP,HOPEMPL.MATRI, HOPEMPL.NOMPRE,HOPEMPL.HORSECT, HOPSECH.LIBCOURT, HOPSECH.LIBELLE, HOPEMPL.GROUPH,
listagg(HOPROLE.UTIL || ' : ' || coalesce(HOPROLE.HORSECT, ' '), '; ')
        within group ( order by HOPROLE.UTIL ) ROL,
valideur.valideur
From HOPEMPL
JOIN HOPSECH ON HOPEMPL.HORSECT = HOPSECH.HORSECT
JOIN HOPCTRA ON HOPEMPL.MATRI = HOPCTRA.MATRI and SYSDATE Between HOPCTRA.DATDEB and HOPCTRA.DATFIN
JOIN HOPROLE ON HOPEMPL.MATRI = HOPROLE.MATRI and SYSDATE between HOPROLE.DATDEB and HOPROLE.DATFIN
LEFT JOIN ( 
select LISTAGG(x.nompre, '; ' ) WITHIN GROUP (ORDER BY x.nompre) as valideur, x.hierar from
( select hopempl.nompre,sfphierd.hierar from hopempl ,sfphierd where hopempl.matri = sfphierd.matri ) x
GROUP By x.hierar
) valideur
on hopempl.grouph = valideur.hierar
 
WHERE 
HOPEMPL.SEITYPPOP not in ('V',' ', 'I')
AND HOPEMPL.MATRI not in ('989897','989898','98989826')
 
GROUP BY (hopempl.seityppop,
         hopempl.matri,
         hopempl.nompre,
         hopempl.horsect,
         hopsech.libcourt,
         hopsech.libelle,
         hopempl.grouph,valideur.valideur)
ORDER BY HOPEMPL.HORSECT