DB2 LUW Procédure stockée qui retourne des lignes pour parrer à un SQL0338N [Résolu]

[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 :

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 :

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 :

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 :

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 :

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 :

SELECT * FROM TABLE( MonUDTF(Parm1, Parm2...) ) AS Table1

[jadey]

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

Code :

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 :

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 :

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 :

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 :

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.

[Mercure]

Pour y voir + clair, colle ici toute la requête telle que tu l'exécutes.

[jadey]

Ne t'en prens qu'a toi même

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
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
SELECT header.DATE_H AS DATE, header.FACTURE_NO, MASTR.ACNO AS NBR_COMPTES, MASTR.ISIN, cag.BASESECURITYID, fim.OTHRLONGNAME, --ramener le libellé de la valeur mère pour les coupons ou le libellé du coupon pour les obligations
CASE
    WHEN fim.INSTRCTGRY='BDT' THEN fim.OTHRLONGNAME
    WHEN fim.INSTRTYPE='CPNS' THEN
           (SELECT fim2.OTHRLONGNAME
            FROM BANCSOPRPRD.FIM_PRMYDTLS fim2
            WHERE cag.BASESECURITYID= fim2.INSTRID)
END AS ISIN_OR_BASEISIN_LIB, BANCSOPRPRD.FCT_GET_PENULTIMATE_WORD_USING_TOKEN(fim.OTHRLONGNAME, ' ') AS maturite_nbr,--même libellé valeur mère et valeur fille Edit(26/11/2010): Au sens maturité
BANCSOPRPRD.FCT_GET_LAST_WORD_USING_TOKEN(fim.OTHRLONGNAME, ' ') AS maturite_lib,--même libellé valeur mère et valeur fille Edit(26/11/2010): Au sens maturité
CASE
    WHEN fim.INSTRCTGRY='BDT' THEN 'OBLIGATION(s)'
    WHEN fim.INSTRTYPE='CPNS' THEN 'COUPON(s)'
END AS grouptype, cag.DISBSECURITYID AS coupons, header.BP_NAME, header.POAHOLDER_NAME, mastr.amount
FROM BANCSOPRPRD.FBL_MASTRDTLS_FACT MASTR
LEFT OUTER JOIN BANCSOPRPRD.CAG_SECBASIS cag ON ( cag.EVENTID IN
                                                   (SELECT EVENTID
                                                    FROM TABLE(BANCSOPRPRD.FCT_GET_PROPER_EVENTIDS4DATE(11, 2010)) /*select camaster.EVENTID
											from BANCSOPRPRD.CAG_CAMASTER camaster
											where camaster.EVENTSTATUS= 'CPTD'
											and month(camaster.EXECUTIONDATE)=11
											and year(camaster.EXECUTIONDATE)=2010*/ /*
2010002920,
2010002922,
2010002923,
2010002924,
2010002925,
2010002926,
2010002927,
2010002928,
2010002929,
2010002930,
2010002935,
2010002931,
2010002932,
2010002933,
2010002934,
2010002936,
2010002937,
2010002938,
2010002939,
2010002940,
2010002941,
2010002942,
2010002943,
2010002944,
2010002945,
2010002946,
2010002947,
2010002948,
2010002949,
2010002950,
2010002951,
2010002952,
2010002953,
2010002954,
2010002955,
2010002956,
2010002957,
2010002958,
2010002959,
2010002960,
2010002961,
2010002962,
2010002963,
2010002964
*/ )
                                                 AND MASTR.ISIN= cag.DISBSECURITYID ) , BANCSOPRPRD.FBL_BILL_FACT bill_fact, BANCSOPRPRD.FBL_BILLHEADER_FACT header, BANCSOPRPRD.FIM_PRMYDTLS fim
WHERE MONTH(DATE) = 11--$P{MOIS}
AND YEAR(DATE) = 2010--$P{ANNEE}
AND MASTR.feetype IN ('P025',
                      'P023',
                      'P022',
                      'P024',
                      'P030')
  AND bill_fact.COM_NO=MASTR.COM_SEQ
  AND header.FACTURE_SEQ=bill_fact.FACTURE_SEQ
  AND fim.INSTRID= MASTR.ISIN
  AND (fim.INSTRCTGRY='BDT'
       OR fim.INSTRTYPE='CPNS')
  AND fim.ISSUERCD='00000000101'
  AND MASTR.BPID_LIABLE='00000000101'
  AND MASTR.BPID_RECIPIENT='00000000101'
  AND header.FACTURE_NO IS NOT NULL
ORDER BY grouptype DESC, maturite_lib DESC, int(maturite_nbr) ASC, isin ASC, MASTR.amount DESC WITH UR

[Mercure]

J'ai retiré tous tes commentaires pour y voir + clair et c'est pas dommage et voila à quoi je suis arrivé. Tiens compte de mes propres commentaires insérés dans l'instruction et fais un essai please.

SELECT header.DATE_H AS DATE, header.FACTURE_NO, MASTR.ACNO AS NBR_COMPTES, MASTR.ISIN, cag.BASESECURITYID, fim.OTHRLONGNAME,
CASE
WHEN fim.INSTRCTGRY='BDT' THEN fim.OTHRLONGNAME
WHEN fim.INSTRTYPE='CPNS' THEN
(SELECT fim2.OTHRLONGNAME
FROM BANCSOPRPRD.FIM_PRMYDTLS fim2
WHERE cag.BASESECURITYID= fim2.INSTRID)
END AS ISIN_OR_BASEISIN_LIB,
BANCSOPRPRD.FCT_GET_PENULTIMATE_WORD_USING_TOKEN(fim.OTHRLONGNAME, ' ') AS maturite_nbr,
BANCSOPRPRD.FCT_GET_LAST_WORD_USING_TOKEN(fim.OTHRLONGNAME, ' ') AS maturite_lib,
CASE
WHEN fim.INSTRCTGRY='BDT' THEN 'OBLIGATION(s)'
WHEN fim.INSTRTYPE='CPNS' THEN 'COUPON(s)'
END AS grouptype,
cag.DISBSECURITYID AS coupons, header.BP_NAME, header.POAHOLDER_NAME, mastr.amount

FROM BANCSOPRPRD.FBL_MASTRDTLS_FACT MASTR
LEFT OUTER JOIN BANCSOPRPRD.CAG_SECBASIS cag ON ? = ?, -- Etablir ici le JOIN de colonnes entre ces 2 tables et pas avec l'UDTF bien sûr

-- Comment fais-tu le JOIN entre les 3 tables ci-dessous et les tables MASTR et CAG ci-dessus ?

BANCSOPRPRD.FBL_BILL_FACT bill_fact,
BANCSOPRPRD.FBL_BILLHEADER_FACT header,
BANCSOPRPRD.FIM_PRMYDTLS fim

WHERE cag.EVENTID IN(SELECT EVENTID FROM TABLE(BANCSOPRPRD.FCT_GET_PROPER_EVENTIDS4DATE(11, 2010))) -- je place l'UDTF sur le WHERE et non pas sur le JOIN sinon SQL0338 comme d'ailleurs bien spécifié dans le message par "The ON clause cannot include any subqueries".

AND MASTR.ISIN= cag.DISBSECURITYID
AND MONTH(DATE) = 11
AND YEAR(DATE) = 2010
AND MASTR.feetype IN ('P025',
'P023',
'P022',
'P024',
'P030')
AND bill_fact.COM_NO=MASTR.COM_SEQ
AND header.FACTURE_SEQ=bill_fact.FACTURE_SEQ
AND fim.INSTRID= MASTR.ISIN
AND (fim.INSTRCTGRY='BDT'
OR fim.INSTRTYPE='CPNS')
AND fim.ISSUERCD='00000000101'
AND MASTR.BPID_LIABLE='00000000101'
AND MASTR.BPID_RECIPIENT='00000000101'
AND header.FACTURE_NO IS NOT NULL

ORDER BY grouptype DESC, maturite_lib DESC, int(maturite_nbr) ASC, isin ASC, MASTR.amount DESC WITH UR

[jadey]

Pour répondre à tes commentaires:

-- Etablir ici le JOIN de colonnes entre ces 2 tables et pas avec l'UDTF bien sûr

C'est la condition

Code :

AND MASTR.ISIN= cag.DISBSECURITYID

que toi tu as fais sortir du 'ON' vers le 'WHERE'

-- Comment fais-tu le JOIN entre les 3 tables ci-dessous et les tables MASTR et CAG ci-dessus ?

Avec ce qui suit:

Code :

1
2
3
AND bill_fact.COM_NO=MASTR.COM_SEQ
AND header.FACTURE_SEQ=bill_fact.FACTURE_SEQ
AND fim.INSTRID= MASTR.ISIN

Cette requête c'est exactement ce que j'ai fait après que tu m'aies répondu la première fois

J'écrierais plutôt :...

Mais le nombre de résulats diffère avec celui obtenu en remplaçant la sous requête directement par son résultat au niveau du 'ON'

[Mercure]

Remonte les 3 fichiers au niveau du JOIN, c'est meilleur et tu dois te retrouver sauf erreur de ma part avec qqch comme çà :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
...
FROM BANCSOPRPRD.FBL_MASTRDTLS_FACT mastr
LEFT OUTER JOIN BANCSOPRPRD.CAG_SECBASIS cag 
ON cag.DISBSECURITYID = mastr.ISIN
 
JOIN BANCSOPRPRD.FBL_BILL_FACT bill_fact 
ON bill_fact.COM_NO = mastr.COM_SEQ
 
JOIN BANCSOPRPRD.FBL_BILLHEADER_FACT header
ON header.FACTURE_SEQ = bill_fact.FACTURE_SEQ
 
JOIN BANCSOPRPRD.FIM_PRMYDTLS fim
ON fim.INSTRID= mastr.ISIN...

Mais le nombre de résulats diffère avec celui obtenu en remplaçant la sous requête directement par son résultat au niveau du 'ON'

Mais est-ce que l'UDTF FCT_GET_PROPER_EVENTIDS4DATE ne te renvoie que les valeurs que tu veux ?

[jadey]

Mais est-ce que l'UDTF FCT_GET_PROPER_EVENTIDS4DATE ne te renvoie que les valeurs que tu veux ?

L'UDTF renvoi exactement les valeurs qu'il faut.

[Mercure]

L'UDTF renvoi exactement les valeurs qu'il faut.

Si c'est le cas, le problème se situe ailleurs dans la requête et l'UDTF ne doit pas être mise en cause et (je fais avec ce que tu me dis), il doit y avoir incohérence entre ce que renvoie l'UDTF et son remplacement direct par des valeurs.

[jadey]

La sous requête DOIT rentrer dans la clause 'ON' si je veux avoir les mêmes résultats c'est tout. Le tout est de trouver un moyen de la faire. L'UDTF même si DETERMINISTIC et sans EXTERNAL ACTION ne passe pas.
Rappel:

4•A function referenced in an ON clause of a full outer join must be deterministic and have no external action.

Mais malheureusement

6•A SQL function or SQL method cannot be used.

Il doit bien y avoir un moyen
Ce problème je l'ai depuis un moment, et on m'a conseillé une MQT il y'a longtemps mais il faudrait pouvoir lui passer mes paramètres tu en penses quoi ?

[Mercure]

La sous requête DOIT rentrer dans la clause 'ON' si je veux avoir les mêmes résultats c'est tout. Le tout est de trouver un moyen de la faire.

Tu n'y arriveras pas car, SQL te l'a déjà dit et je le redis, "The ON clause cannot include any subqueries" et l'UDTF sur le ON est un subquery. Point final.

En revanche, je ne comprends pas pourquoi les résultats sont différents entre l'UDTF et son remplacement par des valeurs en dur dans la mesure où ces dernières sont les mêmes que les valeurs renvoyées par l'UDTF.

Fais une liste des valeurs en dur que tu passes et les valeurs que te renvoie l'UDTF et colle-la ici ou zippe-la en PJ si trop grosse.

Quant au MQT, je dirais NIET car il faudrait le réactualiser et le paramètrer et je pense que ça va ajouter alors une couche de complexité dans un truc déjà bien complexe.

[jadey]

En revanche, je ne comprends pas pourquoi les résultats sont différents entre l'UDTF et son remplacement par des valeurs en dur dans la mesure où ces dernières sont les mêmes que les valeurs renvoyées par l'UDTF. Fais une liste des valeurs

non non non et non j'ai tout fait pour ne pas t'induire en erreur pourtant.
Quand je dis:

Mais le nombre de résulats diffère avec celui obtenu en remplaçant la sous requête directement par son résultat au niveau du 'ON'

je parle du résultat de la requête entière, pas de la sous requête.

Les résultats ne sont pas différents entre l'udtf et son remplacement par ses valeurs renvoyées, mais entre une requête (entière) A avec les valeurs en dur pourvu que ce soit au niveau du 'on' et une requête (entière) B avec l'udtf ou ses valeurs en dur peu importe pourvu que ce soit au niveau du 'where'.
Quand aux valeurs en dur c'est la suite de chiffre en commentaire sur la requête.

Ce constat était tout à fait prévisible, le fait de faire sortir la sous requête du 'on' et la mettre sur le 'where' prive les résultats de tout enregistrement ne réalisant pas la condition de la sous requête parceque justement déplacée sur le 'where'.

Tu n'y arriveras pas car, SQL te l'a déjà dit et je le redis, "The ON clause cannot include any subqueries" et l'UDTF sur le ON est un subquery. Point final.

Imaginons que ce ne soit plus une sous requête mais une table, à ce moment ça passera il suffira juste que les données de cette table changent en fonction de mes paramètres. Ce que je cherche au final c'est une structure de données ressemblant à une table (une vue parametrée peu être si ça existe sur DB LUW ?)

[Mercure]

non non non et non j'ai tout fait pour ne pas t'induire en erreur pourtant.

Il faut croire que si pourtant ! Le moins qu'on puisse dire, c'est que tes explications sèment la confusion, notamment quand tu affirmes que

... le nombre de résulats diffère avec celui obtenu en remplaçant la sous requête directement par son résultat au niveau du 'ON'

Comment peut-on comprendre autre chose que "les résultats diffèrent si remplacement de l'UDTF par une liste de valeurs en dur" ?

Toutefois, je ne me suis sans doute pas été assez concentré sur ton problème sinon j'aurais dû remarquer que tu faisais un LEFT OUTER JOIN avec la table FBL_MASTRDTLS_FACT (mastr) et pas un simple JOIN et c'est ce "LEFT OUTER" qui crée la différence dont tu parles. Autrement dit, tu veux que le recordset (le résultat) contienne parmi d'autres les zones sélectionnées de la table "mastr", que cette dernière matche ou pas avec les valeurs que renvoie l'UDTF. Et c'est là-dessus que tu aurais dû insister...


Ceci dit, essaie ça mais pas sûr :

Code :

1
2
3
FROM BANCSOPRPRD.FBL_MASTRDTLS_FACT MASTR
LEFT OUTER JOIN BANCSOPRPRD.CAG_SECBASIS cag ON cag.DISBSECURITYID = mastr.ISIN
JOIN TABLE(BANCSOPRPRD.FCT_GET_PROPER_EVENTIDS4DATE(11, 2010)) udtf ON udtf.EVENTID = cag.EVENTID

[jadey]

Ceci dit, essaie ça mais pas sûr :

Désolé le résultat (de la requête en entier hein !) est similaire que ce qu'avait donné la sortie de la sous requête du 'ON' vers le 'WHERE'

Imaginons que ce ne soit plus une sous requête mais une table, à ce moment...

Moment de divagation intense méaculpa. Même avec une vue ou MQT ou Common Table Expression (J'en aurait appris des 'trucs') ça reviendrait à écrire un (select.. from...) donc re-sous requête et re SQL0338N

Edit: zut grillé

[Mercure]

Désolé le résultat (de la requête en entier hein !) est similaire que ce qu'avait donné la sortie de la sous requête du 'ON' vers le 'WHERE'

Alors remplace le JOIN par un LEFT JOIN ou LEFT OUTER JOIN dans

Code :

1
2
LEFT JOIN TABLE(BANCSOPRPRD.FCT_GET_PROPER_EVENTIDS4DATE(11, 2010)) udtf ON udtf.EVENTID = cag.EVENTID

[jadey]

Yes ça a apparement l'air d'être ça la solution (LEFT JOIN TABLE) en tout cas le nombre de résultats entre les requêtes avec les valeurs en dur et les requêtes avec LEFT JOIN TABLE de trois mois différents sont les mêmes reste à vérifier les contenus que je viens d'imprimer. ça devrait me prendre un peu de temps. Merci beaucoup beaucoup mercure. En plus tu as donné la soluce juste après que ce soit dissipée l'incompréhension sur le LEFT OUTER JOIN t'es un chef, merci encore.