Pbl requete SQL SQLCODE: -338, SQLSTATE: 42972

Version imprimable

bonjour,

besoin d'aide sur cette requete qui me provoque une erreur sur un environnement DB2 :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 select p.nupers,p.sicivi,p.cosu,p.noraso,p.prenom,p.copost,p.nudepa,p.copred, r.libelo as libeloR, u.nupers as nupersU,u.sicivi as siciviU,u.cosu as cosuU,u.noraso as norasoU,u.prenom as prenomU,u.copost as copostU,u.coraup as coraupU, i.typers from &bib.persid as p, &bib.rfcopo as r LEFT OUTER JOIN &bib.peadru as u ON p.nupers = u.nupers and u.cosu ='0' LEFT OUTER JOIN &bib.rfsici as i ON p.sicivi = i.sicivi and i.cosu ='0' where p.cosu=0 and p.copost = r.copost and p.nudepa = r.nudepa and p.nucomm = r.nucomm
Citation:

Envoyé par doc IBM

-338 AN ON CLAUSE IS INVALID
Explanation: This return code reports a violation of one of the following:

One expression of the predicate must only reference columns of one of
the operand tables of the associated join operator, full join, and
the other expression of the predicate must only reference columns of
the other operand table.
A VALUE or COALESCE function is allowed in the ON clause only when
the join operator is a FULL OUTER JOIN or FULL JOIN.
An operator other than '=' is not allowed in a FULL OUTER JOIN or
FULL JOIN.
A subquery is not allowed in the ON clause.

System Action: The statement cannot be executed. Programmer Response:
Correct the syntax so that it doesn't violate any of the above items within the ON clause

merçi d'avance pour votre aide
A+

si tu déplace tes left outer join

Code:

1
2
3
4
FROM &bib.persid AS p 
LEFT OUTER JOIN &bib.peadru AS u ON p.nupers = u.nupers AND u.cosu = '0'
LEFT OUTER JOIN &bib.rfsici AS i ON p.sicivi = i.sicivi AND i.cosu = '0',
&bib.rfcopo AS r

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT  p.nupers,p.sicivi,p.cosu,p.noraso,p.prenom,p.copost,p.nudepa,p.copred,
        r.libelo as libeloR,
        u.nupers as nupersU,u.sicivi as siciviU,u.cosu as cosuU,u.noraso as norasoU,u.prenom as prenomU,u.copost as copostU,u.coraup as coraupU,
        i.typers
        FROM &bib.persid as p
           INNER JOIN &bib.rfcopo as r
              ON p.cosu=0 and p.copost = r.copost and p.nudepa = r.nudepa and p.nucomm = r.nucomm 
           LEFT OUTER JOIN &bib.peadru as  u 
              ON p.nupers = u.nupers and u.cosu ='0'
           LEFT OUTER JOIN &bib.rfsici as i  
              ON p.sicivi = i.sicivi and i.cosu ='0'
;