Problème CASE

Version imprimable

Problème CASE

Bonjour voici mon problème :

Code:

1
2
3
4
5
6
7
           DECLARE calcul_ETATPROJ varchar2(50);
            BEGIN
                calcul_ETATPROJ := CASE WHEN (SELECT COUNT(*) FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER JOIN GDA_CONVOPER t4 ON t3.NUMOPER=t4.NUMOPER JOIN GDA_CONVENTION t5 ON t4.NUMCONV=t5.NUMCONV WHERE t1.NUMINTDEM = pNUMINT AND (t5.ETATCONV >= 'C130' AND t5.ETATCONV < 'C200')) != 0 THEN 'Convention entre C130 et C200' 
                                        WHEN (SELECT COUNT(*) FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER >= 'O110' AND t3.ETATOPER < 'O190')) != 0  THEN 'Operation entre O110 et O190'
                                        WHEN (SELECT COUNT(*) FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER >= 'O106' AND t3.ETATOPER < 'O190')) != 0  THEN 'Operation entre O106 et O190'
                                        WHEN (SELECT COUNT(*) FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER = 'O100')) != 0 THEN 'Operation egal a O100'
                                   END;

L'erreur : Erreur(24,47): PLS-00103: Symbole "SELECT" rencontré à la place d'un des symboles suivants : ( - + case mod new not null <identificateur> <identificateur entre guillemets> <variable attachée> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <un littéral de chaîne avec spécification de jeu de caractères> <un nombre> <une chaîne SQL entre apostrophes> pipe <constante de chaîne éventuellement entre guillemets avec indication du...

22/01/2020, 10h25
tatayo

Bonjour,
Je ne connais pas trop Oracle, mais il ne manque pas un SELECT ?

Tatayo.
22/01/2020, 10h27
iKeyyZ

Je ne penses pas, en tout cas je ne vois pas trop ou il faudrait le mettre.

Essaye ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
DECLARE
    calcul_ETATPROJ varchar2(50);
BEGIN
    select  CASE WHEN (SELECT COUNT(*) FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER JOIN GDA_CONVOPER t4 ON t3.NUMOPER=t4.NUMOPER JOIN GDA_CONVENTION t5 ON t4.NUMCONV=t5.NUMCONV WHERE t1.NUMINTDEM = pNUMINT AND (t5.ETATCONV >= 'C130' AND t5.ETATCONV < 'C200')) != 0 THEN 'Convention entre C130 et C200' 
                WHEN (SELECT COUNT(*) FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER >= 'O110' AND t3.ETATOPER < 'O190')) != 0  THEN 'Operation entre O110 et O190'
                WHEN (SELECT COUNT(*) FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER >= 'O106' AND t3.ETATOPER < 'O190')) != 0  THEN 'Operation entre O106 et O190'
                WHEN (SELECT COUNT(*) FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER = 'O100')) != 0 THEN 'Operation egal a O100'
            END
    into    calcul_ETATPROJ
    from    dual;
end;

A comparer aussi avec when exists (select 1 from ... where ...) then ... qui évite de devoir terminer le comptage pour déterminer le résultat.

22/01/2020, 11h46
iKeyyZ

Super ça marche, je te remercie

Autre solution sans CASE (évite de faire un count(*))

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
28
29
DECLARE
 calcul_ETATPROJ varchar2(50);
 v NUMBER;
BEGIN
  BEGIN
    SELECT 1 INTO v FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER JOIN GDA_CONVOPER t4 ON t3.NUMOPER=t4.NUMOPER JOIN GDA_CONVENTION t5 ON t4.NUMCONV=t5.NUMCONV 
    WHERE t1.NUMINTDEM = pNUMINT AND (t5.ETATCONV >= 'C130' AND t5.ETATCONV < 'C200') AND ROWNUM = 1;
    calcul_ETATPROJ := 'Convention entre C130 et C200';
  EXCEPTION WHEN NO_DATA_FOUND THEN    
    BEGIN
      SELECT 1 INTO v FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER
      WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER >= 'O110' AND t3.ETATOPER < 'O190') AND ROWNUM = 1;
      calcul_ETATPROJ := 'Operation entre O110 et O190';
    EXCEPTION WHEN NO_DATA_FOUND THEN    
      BEGIN  
        SELECT 1 INTO v FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER
        WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER >= 'O106' AND t3.ETATOPER < 'O190') AND ROWNUM = 1;
        calcul_ETATPROJ := 'Operation entre O106 et O190';
      EXCEPTION WHEN NO_DATA_FOUND THEN 
        BEGIN
         SELECT 1 INTO v FROM GDA_DA t1 JOIN GDA_DA_OPER t2 ON t1.NUMDA=t2.NUMDA JOIN GDA_OPERATION t3 ON t2.NUMOPER=t3.NUMOPER
         WHERE t1.NUMINTDEM = pNUMINT AND (t3.ETATOPER = 'O100') AND ROWNUM = 1;
         calcul_ETATPROJ := 'Operation egal a O100';
        EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
        END;
      END;
    END;
  END;   
END;