Discussion :

[jadey]

Hello les gens,
Quelqu'un pourait t il me fournir la syntaxe adéquate pour la création d'une procédure stockée parametrée (nommons la PSP) retournant plusieurs enregistrements ?
Le but étant de pouvoir par la suite réaliser une jointure externe avec le résultat que retournerais PSP avec quelque chose ressemblant à:

Code :

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

1
2
3
4
5
6
7
SELECT ... FROM table1,
LEFT OUTER JOIN CAG_SECBASIS cag
		 ON ( cag.champ1=table1.champ1
		 AND cag.EVENTID IN (PSP(9, 2010))
,table2
WHERE ...
ORDER BY...

sans me prendre de SQL0338N
Merci pour toutes vos contributions.

Edit:
ça devient compliqué je ne pense pas que la solution avec la procédure stockée soit bonne. Il est écrit ici que:

An ON clause associated with a JOIN operator or in a MERGE statement is not valid for one of the following reasons.
1•The ON clause cannot include any subqueries.
2•Column references in an ON clause must only reference columns of tables that are in the scope of the ON clause.

3•Scalar fullselects are not allowed in the expressions of an ON clause.
4•A function referenced in an ON clause of a full outer join must be deterministic and have no external action.
5•A dereference operation (->) cannot be used.

6•A SQL function or SQL method cannot be used.
7•The ON clause cannot include an XMLQUERY or XMLEXISTS expression.

Remarquez quand même que les conditions 4 et 6 se "contredisent"


Je viens de trouver ce post: http://bytes.com/topic/db2/answers/8...error-sql0338n
La personne propose de remplacer:

Code :

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

1
2
3
4
5
6
7
LEFT OUTER JOIN 
       ad_com  CMD 
    ON PH.cmd_cd = CMD.cmd_cd 
   AND CMD.ts = (SELECT max(ts) 
                   FROM ad_com CMD_SUB 
                  WHERE CMD_SUB.v_frm <= 100 
                    AND CMD_SUB.cmd_cd = PH.cmd_cd);

Par

Code :

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

1
2
3
4
5
6
7
LEFT OUTER JOIN 
       (SELECT cmd_cd, 
               max(ts) as ts 
          FROM ad_com 
         WHERE v_frm <= 100 
         GROUP BY cmd_cd) CMD 
    ON PH.cmd_cd = CMD.cmd_cd

Je ne vois pas comment transposer cette solution à mon exemple. Une aide serait plus qu'appréciée.

[Mercure]

J'écrierais plutôt :

Code :

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

1
2
3
4
5
6
SELECT ... FROM table1,
LEFT OUTER JOIN CAG_SECBASIS cag
ON cag.champ1=table1.champ1
WHERE cag.EVENTID IN (Select MaColonne From table(PSP(9, 2010)))
  AND ...
ORDER BY...

En fait, ce que tu appelles "PSP" n'a pas lieu ici d'être une procédure stockée. Tu dois écrire à la place une fonction table (User Defined Table Function - UDTF). En voici un exemple qui te montre comment ce type de fonction se présente :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION MonUDTF ( Parm1 Decimal (9, 2), Parm2 Char (4) ... )                 
RETURNS TABLE (PCol1char(3), PCol2 date, PCol3 Numeric(13, 0), ... PColN decimal(13, 2))                             
LANGUAGE SQL                                      
SPECIFIC Exemple                                    
NOT DETERMINISTIC                                 
READS SQL DATA                                    
CALLED ON NULL INPUT                               
NO EXTERNAL ACTION                                
DISALLOW PARALLEL                                 
RETURN SELECT Col1, Col2, Col3, ... ColN from MaTable WHERE Col1 = Parm1 And Col2 = Parm2 ...

NB: il est possible que certains paramètres indiqués dans l'exemple ne soient pas supportés par ton OS. Supprime le paramètre concerné le cas échéant.

J'appelle ensuite cette fonction comme suit :

Code :

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

Select * from table( MonUDTF(Parm1, Parm2...) ) as Table1

[jadey]

Merci d'avoir répondu aussi vite.
En sortant

Code :

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

cag.EVENTID IN (SELECT MaColonne FROM TABLE(PSP(9, 2010)))

vers le where comme tu le propose je n'ai même plus besoin d'utiliser PSP puisque je peux mettre directement la sous requête dans le where ce qui donne:

Code :

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

1
2
3
4
5
6
7
8
SELECT ... FROM table1,
LEFT OUTER JOIN CAG_SECBASIS cag
ON cag.champ1=table1.champ1
WHERE cag.EVENTID IN (select camaster.EVENTID
											from BANCSOPRPRD.CAG_CAMASTER camaster
											where camaster.EVENTSTATUS= 'CPTD'
											and month(camaster.EXECUTIONDATE)=09											and year(camaster.EXECUTIONDATE)=2010			)  AND ...
ORDER BY...

Seulement je constate que les résultats retournés ne sont pas les même que ceux de la requête:

Code :

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

1
2
3
4
5
SELECT ... FROM table1,
LEFT OUTER JOIN CAG_SECBASIS cag
ON cag.champ1=table1.champ1
WHERE cag.EVENTID IN ('111', '112', '113')  AND ...
ORDER BY...

'111', '112', '113' étant le résultat de la sous requête avec les paramètre 09 et 2010

Question: MonUDTF peut elle être DETERMINISTIC ? Si oui cela pourrait bien résoudre mon problème.

[Mercure]

Il faut évidemment que la sous-requête du IN() ne te renvoie que les valeurs sélectionnées (à savoir 111, 112 et 113). Autrement dit, arrange-toi pour que la table camaster.EVENTID ne contienne ou ne te renvoie que les valeurs sélectionnées.

Edit : L'UDTF peut tout à fait être "DETERMINISTIC".

[jadey]

bizzare je viens de créer:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE FUNCTION FCT_GET_PROPER_EVENTIDS4DATE ( MOIS INTEGER, ANNEE INTEGER )                 
RETURNS TABLE (EVENTID BIGINT)
LANGUAGE SQL                                      
SPECIFIC FCT_GET_PROPER_EVENTIDS4DATE                                    
DETERMINISTIC                                 
READS SQL DATA                                    
CALLED ON NULL INPUT                               
NO EXTERNAL ACTION                                
 
RETURN	select camaster.EVENTID
				from BANCSOPRPRD.CAG_CAMASTER camaster
				where camaster.EVENTSTATUS= 'CPTD'
				and month(camaster.EXECUTIONDATE)=MOIS
				and year(camaster.EXECUTIONDATE)=ANNEE

Mais quand j'essaie de faire un appel:

Code :

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

SELECT * FROM TABLE( FCT_GET_PROPER_EVENTIDS4DATE(9, 2010) ) AS Table1

j'ai droit à DB2 Database Error: ERROR [42884] [IBM][DB2/AIX64] SQL0440N No authorized routine named "FCT_GET_PROPER_EVENTIDS4DATE" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

Edit: Il fallait rajouter le suffixe de la base.
Bon la fonction retourne bien ce qu'il faut mais impossible de l'utiliser dans la sous requête sans me prendre le SQL0338N.
J'ai appris un nouveau 'truc', mais je ne suis gère plus avancé

[Mercure]

Integer implique de passer les paramètres de la fonction table en binaire sur l'instruction SELECT. Aussi, au lieu d'Integer, fais un essai en définissant MOIS et ANNEE en numérique étendu ( Numeric ) ou en packé ( Decimal ) si supporté, comme illustré dans mon exemple précédent ci-dessus.

Edit : A quoi sert de donner un nom spécifique identique au nom de la fonction ? A rien ! Alors, indique un nom vraiment spécifique.

Je ne comprends pas pour le moment pourquoi tu encaisses un SQL0338.Je cherche et te dis si je trouve qqch.