Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Recherche d'une valeur d'une autre table


Sujet :

SQL Oracle

  1. #21
    Membre à l'essai
    C'est parfait.
    Je vous remercie beaucoup pour vos contributions et je vous souhaite un bon week-end.

  2. #22
    Membre à l'essai
    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.

  3. #23
    Membre averti
    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 ?

  4. #24
    Membre à l'essai
    Bonjour,

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

  5. #25
    Membre averti
    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 ?

  6. #26
    Membre à l'essai
    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

  7. #27
    Membre averti
    Il nous faudrait le code et le motif de l'erreur pour pouvoir aider au mieux

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

  9. #29
    Membre averti
    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

  10. #30
    Rédacteur/Modérateur

    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 SUM
    Donc 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.
    N'oubliez pas le bouton Résolu si vous avez obtenu une réponse à votre question.

  11. #31
    Membre à l'essai
    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

  12. #32
    Membre averti
    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

  13. #33
    Membre à l'essai
    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

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

  15. #35
    Membre à l'essai
    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

  16. #36
    Expert éminent sénior
    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

  17. #37
    Membre à l'essai
    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

  18. #38
    Membre averti
    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

  19. #39
    Membre à l'essai
    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.

  20. #40
    Membre à l'essai
    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