IBM Support

Message SQL0338 Errors on Joins

Troubleshooting


Problem

This document explains changes were made in V6R1 that result in an SQL0338 message (Invalid join order) being issued if a table is referred to outside the scope of its join.

Resolving The Problem

Changes were made in V6R1 (IBM i 6.1) so that an SQL0338 message (Join Predicate not valid) is issued if a table is referred to outside the scope of its join. This will result in some join queries failing in V6R1 that worked in previous releases. This change is also documented in the V6R1 Memo to Users. This change was made so that DB2 for i behavior matched the SQL standard. The change in behavior was implemented on a release boundary and documented within the Memorandum To Users book. The SQL0338 is only issued at the time the SQL statement is run. It is not issued when the statement is prepared or compiled, because the syntax of the statement is correct, even though is will give the SQL0338 message when it is run.


Any queries that get an SQL0338 message will have to be changed to run on V6R1. Below you will find examples of DB2’s pre-V6R1 implementation of automatically rewriting the query to help you match the pre-V6R1 implementation.

Example 1

SELECT a.c1 FROM tabA a LEFT OUTER JOIN
tabC c LEFT OUTER JOIN tabB b ON a.c1 = b.c1 ON b.c1n = c.c1n


This Select statement was rewritten to the following query using lateral correlation because the first join condition (a.c1 = b.c1) references a column in tabA that was out of scope of the left outer join between tables tabC and tabB.

SELECT a.c1 FROM tabA a LEFT OUTER JOIN
LATERAL (SELECT b.c1n bc1n, c.c1n cc1n FROM tabC c LEFT OUTER JOIN tabB b ON a.c1 = b.c1) j1
ON bc1n = cc1n


Example 2

SELECT a.c1 FROM tabA a, tabB b INNER JOIN tabC c ON a.c1 = c.c1
This Select statement was rewritten to the following query using lateral correlation because the first join condition (a.c1 = c.c1) references a column in tabA that was out of scope of the inner join between tables tabB and tabC.

SELECT a.c1 FROM tabA a, LATERAL(SELECT * FROM tabB b INNER JOIN tabC c ON a.c1 = c.c1) j2

If it was not your intent to use lateral correlation as part of the join definition, the query will need to be rewritten so that the join conditions only reference tables in the same join scope.


Example 3

select brk.companynameid from
brk
join cn on brk.companynameid = cn.companynameid,
api
left outer join vdr on api.vendorid = vdr.vendorid
left outer join apr on api.apinvoiceid = apr.reconciledapinvid
left outer join app on apr.reconcilingappayid = app.appaymentid
left outer join cvr on api.coverageid = cvr.coverageid
left outer join mbr on cvr.memberid = mbr.memberid
left outer join mg on api.groupid = mg.groupid
left outer join fgv on mg.groupid = fgv.groupid
left outer join cg on mg.groupid = cg.groupid
left outer join ca on brk.companyaddressid = ca.companyaddressid
left outer join st on st.stateid = ca.stateid
left outer join hc on hc.healthcompanyid = api.healthcompanyid
left outer join bc on bc.brokercompanyid = brk.brokercompanyid

We can make this query work by moving file brk closer to where it is referenced and by replacing a comma with CROSS JOIN so that we can use parentheses to change the order in which the joins are done.

select brk.companynameid from
cn
join brk on brk.companynameid = cn.companynameid cross join
(api
left outer join vdr on api.vendorid = vdr.vendorid
left outer join apr on api.apinvoiceid = apr.reconciledapinvid
left outer join app on apr.reconcilingappayid = app.appaymentid
left outer join cvr on api.coverageid = cvr.coverageid
left outer join mbr on cvr.memberid = mbr.memberid
left outer join mg on api.groupid = mg.groupid
left outer join fgv on mg.groupid = fgv.groupid
left outer join cg on mg.groupid = cg.groupid)
left outer join ca on brk.companyaddressid = ca.companyaddressid
left outer join st on st.stateid = ca.stateid
left outer join hc on hc.healthcompanyid = api.healthcompanyid
left outer join bc on bc.brokercompanyid = brk.brokercompanyid

Each query needs to be looked at individually. This document gives some ideas on how to modify queries; however, the client is responsible for changing his/her particular queries as necessary.

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0"}]

Historical Number

513762951

Document Information

Modified date:
11 November 2019

UID

nas8N1013221