[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199

[beckyh]

[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199 - Keyword OUTER not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.


SQL = "SELECT BPKQCD, BPKRCD, BPGDNB, BPBAVA/100 as Balance, BSJ6CD, BSJ7CD, BSNBCD, BSBEVA FROM YABPREP FULL OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPGDNB = BSNBCD AND BPKRCD = BSJ7CD WHERE BPKQCD = '6121' AND BPGDNB = 'APR05' ORDER BY BPKRCD"

why doesn't this sql statement work? please HELP!!!

 

[Mercury2]

Remove 'FULL' (not allowed on iSeries), replace 'WHERE' by 'AND' and give it a try :

SQL = "SELECT BPKQCD, BPKRCD, BPGDNB, BPBAVA/100 as Balance, BSJ6CD, BSJ7CD, BSNBCD, BSBEVA
FROM YABPREP
OUTER JOIN YABSREP
ON BPKQCD = BSJ6CD
AND BPGDNB = BSNBCD
AND BPKRCD = BSJ7CD
AND BPKQCD = '6121'
AND BPGDNB = 'APR05'

ORDER BY BPKRCD

 

[beckyh]

Mercury2 - I am still getting the same error...

[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199 - Keyword OUTER not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.]

Is OUTER allowed?

 

[Mercury2]

It's LEFT OUTER JOIN or RIGHT OUTER JOIN, was too optimistic, sorry )

 

[beckyh]

mercury2 - do you recommend any books or websites for this type of info? i am brand new to sending SQL to DB2.

 

[Mercury2]

Check out (SQL) here

http://minilien.com/?B8H1PQhrVw

or the main iSeries web-page here

http://publib.boulder.ibm.com/iseries/

 

[beckyh]

thanks - seem to be great resources!!! do you know any particular place in this documentation that addresses JOINS? Can't seem to find it....

 

[beckyh]

Found it...

DB2 UDB for iSeries SQL Programming Concepts V5R2

Simulating a Full Outer Join
Like the left and right outer joins, a full outer join returns matching rows from both tables. However, a full outer join also returns non-matching rows from both tables; left and right. While DB2 UDB for iSeries does not support full outer join syntax, you can simulate a full outer join by using a left outer join and a right exception join.

 

[Mercury2]

BTW, you could create a stored procedure instead on the iSeries server and call it from the client along with variable parameters to gain control over the database on the iSeries side.