Oracle8.1.7: Problème DECODE PL/SQL

[payetachnek]

bonjour,

j'ai un souci de procédure PL/Sql.
Oracle Discoverer me génére des requetes SQL, ces requetes je les mets dans une procedure stockée pour générer un fichier CSV j'ai trois type d'erreurs !!

Erreur1 PLS-00103: Symbole "CASE" rencontré à la place d'un des symboles suivants :
Erreur2 PLS-00222: la fonction nommée 'DECODE' n'existe pas dans cette étendue

mes questions Comment remplacer le DECODE dans la condition WHERE??
peut on utiliser CASE WHEN en 8.1.7 ?
Merci


REQUETE1: condition where

Code :

1
2
3
4
5
6
7
8
9
10
11
12
WHERE 
((DG_TYPEMARCHECONTRAT = DH_DG_MARCHECONTRAT AND DG_NUMMARCHECONTRAT = DH_DG_NUMMARCHECONTRAT)
AND (DH_DG_MARCHECONTRAT = JP_DG_TYPEMARCHECONTRAT AND DH_DG_NUMMARCHECONTRAT = JP_DG_NUMMARCHECONTRAT AND DH_NUMLOT = JP_DH_NUMLOT)
AND (JP_DG_TYPEMARCHECONTRAT = KU_JP_TYPEMARCHECONTRAT AND JP_DG_NUMMARCHECONTRAT = KU_JP_NUMMARCHECONTRAT AND JP_DH_NUMLOT = KU_JP_NUMLOT AND JP_NUMLIGNEFOURNISSEUR = KU_JP_NUMLIGNEFOURNISSEUR)
--ERREUR DECODE	and (CODE_OPERATION = DECODE(KU_KH_CODEOPERATIONSERVICE,' ',KU_KI_CODEOPERATIONPROGRAMMEE,KU_KH_CODEOPERATIONSERVICE)))
AND (qry5.EXERCICE = TO_NUMBER(TO_CHAR(qry5.DATE_DEBUT,'YYYY')))
AND (TO_CHAR(qry5.DATE_DEBUT,'YYYY') = TO_CHAR(SYSDATE-1,'YYYY'))
AND (TO_CHAR(qry5.DATE_FIN,'YYYY') > TO_CHAR(qry5.DATE_DEBUT,'YYYY'))
AND (qry3.JP_JM_CODETYPEFOURNISSEUR = 'TI')  AND
(DATE_MAJ >= TO_DATE(DateMinTrav,'DD/MM/YYYY') AND
DATE_MAJ <= TO_DATE(DateMaxTrav,'DD/MM/YYYY')))
ORDER BY DG_NUMMARCHECONTRAT ASC

[laurentschneider]

non, case n'est pas supporté en PLSQL 8i

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SQL> SELECT version FROM v$instance;                                                                                                                                            
VERSION
-----------------
8.1.7.4.0
 
SQL> declare cursor c IS SELECT case 1 when 1 then 1 end x FROM dual; begin NULL; end;
  2  /
declare cursor c IS SELECT case 1 when 1 then 1 end x FROM dual; begin NULL; end;
                           *
ERROR at line 1:
ORA-06550: line 1, COLUMN 28:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

par contre tu dois pouvoir employer decode

Code :

1
2
3
4
5
 
SQL> declare cursor c IS SELECT decode(1,1,1) x FROM dual; begin NULL; end;
2  /
 
PL/SQL procedure successfully completed.

[dyvim]

Essaye comme çà ca devrait mieux marcher...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WHERE DG_TYPEMARCHECONTRAT = DH_DG_MARCHECONTRAT
  AND DG_NUMMARCHECONTRAT = DH_DG_NUMMARCHECONTRAT
  AND DH_DG_MARCHECONTRAT = JP_DG_TYPEMARCHECONTRAT 
  AND DH_DG_NUMMARCHECONTRAT = JP_DG_NUMMARCHECONTRAT 
  AND DH_NUMLOT = JP_DH_NUMLOT
  AND JP_DG_TYPEMARCHECONTRAT = KU_JP_TYPEMARCHECONTRAT 
  AND JP_DG_NUMMARCHECONTRAT = KU_JP_NUMMARCHECONTRAT 
  AND JP_DH_NUMLOT = KU_JP_NUMLOT 
  AND JP_NUMLIGNEFOURNISSEUR = KU_JP_NUMLIGNEFOURNISSEUR
  AND CODE_OPERATION = DECODE(KU_KH_CODEOPERATIONSERVICE,
                              ' ',KU_KI_CODEOPERATIONPROGRAMMEE,
		        			   KU_KH_CODEOPERATIONSERVICE)
  AND qry5.EXERCICE = TO_NUMBER(TO_CHAR(qry5.DATE_DEBUT,'YYYY'))
  AND TO_CHAR(qry5.DATE_DEBUT,'YYYY') = TO_CHAR(SYSDATE-1,'YYYY')
  AND TO_CHAR(qry5.DATE_FIN,'YYYY') > TO_CHAR(qry5.DATE_DEBUT,'YYYY')
  AND qry3.JP_JM_CODETYPEFOURNISSEUR = 'TI'
  AND DATE_MAJ >= TO_DATE(DateMinTrav,'DD/MM/YYYY') 
  AND DATE_MAJ <= TO_DATE(DateMaxTrav,'DD/MM/YYYY')
ORDER BY DG_NUMMARCHECONTRAT ASC

Pour info, si tu n'as que des AND dans tes clauses tes parenthèses ne servent à rien...

[payetachnek]

merci,

hier me suis apperçu que le case ne fonctionnait pas en 8i (j'ai été trompé par le client 10g qui est sur mon poste). je l'ai effectivement remplacé par un decode.

Cependant, je ne peux pas utiliser le decode dans une condition WHERE est-ce normale en 8i??? et si oui comment by-passer ce problème

merci,

ex qui ne fonctionne pas:
CODE_OPERATION = DECODE(KU_KH_CODEOPERATIONSERVICE,' ',KU_KI_CODEOPERATIONPROGRAMMEE,KU_KH_CODEOPERATIONSERVICE)

[payetachnek]

Envoyé par dyvim

Pour info, si tu n'as que des AND dans tes clauses tes parenthèses ne servent à rien...

désolé c'est du code généré je n'y suis pour rien (cf discoverer d'oracle)

et ça ne fonctionne malheureusement pas

[laurentschneider]

que veux-tu dire par "ça ne marche pas"

Code :

1
2
3
4
5
 
SQL> declare cursor c IS SELECT * FROM x WHERE CODE_OPERATION = DECODE(KU_KH_CODEOPERATIONSERVICE,
' ',KU_KI_CODEOPERATIONPROGRAMMEE,KU_KH_CODEOPERATIONSERVICE); begin NULL; end;
2  /
PL/SQL procedure successfully completed.

[payetachnek]

je ne suis pas dans un curseur mais dans une loop pour fichier

for i in(
select ...........
where)
) loop
UTL_FILE.put (vo_FileDesc,i.champ1);

ça vient peut etre de là !

[laurentschneider]

donc un curseur implicite, ce qui devrait aussi marcher

Code :

1
2
3
4
 
SQL> exec FOR f IN ( SELECT * FROM x WHERE CODE_OPERATION = DECODE(KU_KH_CODEOPERATIONSERVICE,' ',KU_KI_CODEOPERATIONPROGRAMMEE,KU_KH_CODEOPERATIONSERVICE) ) loop NULL; end loop
 
PL/SQL procedure successfully completed.

[payetachnek]

voici la loop global qui ne fonctionne pas

for i in (
SELECT DISTINCT
'Marché' as champ1,
CODE_OPERATION as champ2,
DG_NUMMARCHECONTRAT as champ3,
'' as champ4,
DG_JK_CODEMODEEXECUTION as champ5,
'' as champ6,
'' as champ7,
'' as champ8,
'' as champ9,
'' as champ10,
'' as champ11,
DECODE(DG_DATENOTIFICATION,DG_DATEMAJ,'Création','Modification') as champ12,
DG_OBJETMARCHECONTRAT as champ13,
TO_CHAR(DG_DATENOTIFICATION,'DD/MM/YY') as champ14,
TO_CHAR(DG_DATEDEBUT,'DD/MM/YY') as champ15,
TO_CHAR(DG_DATEFIN,'DD/MM/YY') as champ16,
'' as champ17,
'' as champ18,
DECODE(DG_MONTANTINITIAL,DG_MONTANTREEL,DG_MONTANTINITIAL,DG_MONTANTREEL-DG_MONTANTINITIAL) as champ19,
'' as champ20,
'' as champ21,
'' as champ22,
'' as champ23,
'' as champ24,
'' as champ25
FROM
( SELECT DG_DATEMAJ, DG_DATENOTIFICATION, DG_BF_CODETIERS,
DG_DATERECONDUCTION, DG_DATEREVISIONPREVU, DG_MONTANTMINICONTRAT, DG_MONTANTREEL,
DG_DERNUMAVENANT, DG_DERNUMLOTNUMPROLONGATION, DG_CODEDEVISE, DG_MTREELAVTCONVERSION,
DG_NUMMARCHECONTRAT, DG_NUMMARCHENOVA, DG_MONTANTINITIAL, DG_PROCEDUREADAPTEE,
DG_OBJETMARCHECONTRAT, DG_SIGNATUREMAJ, DG_RESSOURCEAFFECTEE, DG_DUREECONTRATANNEE,
DG_DUREECONTRATMOIS, DG_VALIDE, DG_ARCHIVEOUINON, DG_MTINITIALAVTCONVERSION,
DG_DATEFIN, DG_DATELIMITEREGLEMENT, DG_CONTRATDEPENRECETTE, DG_CONTROLEDEPASSEMENT,
DG_CONTROLEBLOQUANT, DG_CIRCUITAPPROBATION, DG_DATECOMMISSION, DG_DATEDEBUT,
DG_DATEDECOMPTEDEFINITIF, DG_JI_CODEOBJETMARCHE, DG_JJ_CODEMODEPASSATION,
DG_JK_CODEMODEEXECUTION, DG_JO_CODEMODEREGLEMENT, DG_PERIODEPREAVIS,
DG_PERIODICITEPAIEMENT, DG_NUMLIGNECIRCUITAPP, DG_TYPEMARCHECONTRAT,
DG_GC_CODENATURECONTRAT FROM DG_MARCHE ) qry1,
( SELECT DH_DATEMAJ, DH_MONTANTREEL, DH_SIGNATUREMAJ, DH_MTREELAVTCONVERSION,DH_NUMLOT,
DH_MONTANTINITIAL, DH_LIBELLE2, DH_DELAIPAIEMENT,DH_MTINITIALAVTCONVERSION,
DH_DG_MARCHECONTRAT, DH_DG_NUMMARCHECONTRAT, DH_DATEFIN, DH_CODESERVICECOMPTABLE,
DH_CODESERVICETECHNIQUE,DH_CODESERVICEADMINISTRATIF, DH_HORSDELAI, DH_DATEDEBUT,
DH_JL_CODETYPECAUTION, DH_LIBELLE FROM DH_LOTMAR ) qry2,
( SELECT JP_BF_CODETIERS, JP_MONTANTREEL, JP_DATEMAJ,
JP_MTREELAVTCONVERSION, JP_MONTANTINITIAL, JP_MTINITIALAVTCONVERSION,JP_DG_NUMMARCHECONTRAT,
JP_DG_TYPEMARCHECONTRAT, JP_DH_NUMLOT,JP_NUMLIGNEFOURNISSEUR, JP_JM_CODETYPEFOURNISSEUR,
JP_SIGNATUREMAJ,JP_GO_NUMRIBTIERS FROM JP_FOURNLOT ) qry3,
( SELECT KU_FA_CODETVA, KU_TYPEACHAT, KU_MONTANTHT, KU_KG_CODEFAMILLE,KU_KH_CODEOPERATIONSERVICE,
KU_JP_NUMLOT, KU_JP_NUMMARCHECONTRAT,KU_JP_TYPEMARCHECONTRAT, KU_JP_NUMLIGNEFOURNISSEUR, KU_DI_NUMAVENANT,
DECODE(KU_KH_CODEOPERATIONSERVICE,' ',KU_KI_CODEOPERATIONPROGRAMMEE,KU_KH_CODEOPERATIONSERVICE),
KU_NUMLIGNELOTFAMILLE, KU_TYPEBESOIN, KU_KI_CODEOPERATIONPROGRAMMEE,
KU_MONTANTTTC, KU_MONTANTTVA1, KU_MONTANTTVA2 FROM KU_FOLOFA ) qry4,
( SELECT CODE_OPERATION, DATE_DEBUT, DATE_FIN, DATE_MAJ, LIBELLE,EXERCICE FROM (SELECT KH_AV_EXERCICE Exercice, KH_CODEOPERATIONSERVICE Code_operation, KH_DATEDEBUT Date_debut, KH_DATEFIN Date_fin, KH_DATEMAJ Date_maj, KH_LIBELLE Libelle FROM KH_OPESER UNION SELECT KI_AV_EXERCICE Exercice, KI_OPEPROG.KI_CODEOPERATIONPROGRAMMEE Code_operation,
KI_DATEDEBUT Date_debut, KI_DATEFIN Date_fin, KI_DATEMAJ Date_maj, KI_LIBELLE Libelle FROM KI_OPEPROG) qry51
) qry5
WHERE
DG_TYPEMARCHECONTRAT = DH_DG_MARCHECONTRAT
AND DG_NUMMARCHECONTRAT = DH_DG_NUMMARCHECONTRAT
AND DH_DG_MARCHECONTRAT = JP_DG_TYPEMARCHECONTRAT
AND DH_DG_NUMMARCHECONTRAT = JP_DG_NUMMARCHECONTRAT
AND DH_NUMLOT = JP_DH_NUMLOT
AND JP_DG_TYPEMARCHECONTRAT = KU_JP_TYPEMARCHECONTRAT
AND JP_DG_NUMMARCHECONTRAT = KU_JP_NUMMARCHECONTRAT
AND JP_DH_NUMLOT = KU_JP_NUMLOT
AND JP_NUMLIGNEFOURNISSEUR = KU_JP_NUMLIGNEFOURNISSEUR
AND CODE_OPERATION = DECODE(KU_KH_CODEOPERATIONSERVICE,
' ',KU_KI_CODEOPERATIONPROGRAMMEE,
KU_KH_CODEOPERATIONSERVICE)
AND qry5.EXERCICE = TO_NUMBER(TO_CHAR(qry5.DATE_DEBUT,'YYYY'))
AND TO_CHAR(qry5.DATE_DEBUT,'YYYY') = TO_CHAR(SYSDATE-1,'YYYY')
AND TO_CHAR(qry5.DATE_FIN,'YYYY') > TO_CHAR(qry5.DATE_DEBUT,'YYYY')
AND qry3.JP_JM_CODETYPEFOURNISSEUR = 'TI'
AND DATE_MAJ >= TO_DATE(DateMinTrav,'DD/MM/YYYY')
AND DATE_MAJ <= TO_DATE(DateMaxTrav,'DD/MM/YYYY')
ORDER BY DG_NUMMARCHECONTRAT ASC
) loop
UTL_FILE.put (vo_FileDesc,i.champ1);
UTL_FILE.put (vo_FileDesc,i.champ2);
UTL_FILE.put (vo_FileDesc,i.champ3);
UTL_FILE.put (vo_FileDesc,i.champ4);
UTL_FILE.put (vo_FileDesc,i.champ5);
UTL_FILE.put (vo_FileDesc,i.champ6);
UTL_FILE.put (vo_FileDesc,i.champ7);
UTL_FILE.put (vo_FileDesc,i.champ8);
UTL_FILE.put (vo_FileDesc,i.champ9);
UTL_FILE.put (vo_FileDesc,i.champ10);
UTL_FILE.put (vo_FileDesc,i.champ11);
UTL_FILE.put (vo_FileDesc,i.champ12);
UTL_FILE.put (vo_FileDesc,i.champ13);
UTL_FILE.put (vo_FileDesc,i.champ14);
UTL_FILE.put (vo_FileDesc,i.champ15);
UTL_FILE.put (vo_FileDesc,i.champ16);
UTL_FILE.put (vo_FileDesc,i.champ17);
UTL_FILE.put (vo_FileDesc,i.champ18);
UTL_FILE.put (vo_FileDesc,i.champ19);
UTL_FILE.put (vo_FileDesc,i.champ20);
UTL_FILE.put (vo_FileDesc,i.champ21);
UTL_FILE.put (vo_FileDesc,i.champ22);
UTL_FILE.put (vo_FileDesc,i.champ23);
UTL_FILE.put (vo_FileDesc,i.champ24);
UTL_FILE.put (vo_FileDesc,i.champ25);
UTL_FILE.new_line (vo_FileDesc,1);
end loop;

[McM]

Envoyé par payetachnek

je ne suis pas dans un curseur mais dans une loop pour fichier

for i in(
select ...........
where)
) loop
UTL_FILE.put (vo_FileDesc,i.champ1);

ça vient peut etre de là !

Tu peux pas modifier et créer un curseur ?

Code :

1
2
3
4
5
6
DECLARE... cursor c IS SELECT .....;
BEGIN
 FOR i IN c LOOP
   UTL_FILE..... 
 END LOOP;
END;

[payetachnek]

si je vais essayer pour voir la réaction du compilateur!
je vous tiens au courant,

merci a tous et bon week

[laurentschneider]

Envoyé par 8.1.7.4

SQL> begin
2 for i in (
3 SELECT 1 FROM
4 ( SELECT DECODE(1,1,1) FROM dual )
5 WHERE 1 = DECODE(1,1,1)
6 ) loop
7 null;
8 end loop;
9 end;
10 /
WHERE 1 = DECODE(1,1,1)
*
ERROR at line 5:
ORA-06550: line 5, column 15:
PLS-00222: no function with name 'DECODE' exists in this scope
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored

ça a l'air d'un bug, et ça marche bien dans 9i.

la prochaine fois, réduis stpl ton code au minimum...

[payetachnek]

merci à tous pour votre aide,

cdt,