Re: SQL0338 on 6.1

James Perkins wrote:

I am getting this error (SQL0338) on 6.1. I found
documentation about this error, but I don't really
understand why so I'm hoping an SQL guru can help me.

http://publib.boulder.ibm.com/infocenter/systems/topic/rzaq9/rzaq9.pdf

Here is the SQL statement. It works fine on 5.4, but
not on 6.1. If i keep move the "USERS" table to be
after the JOIN clause all works okay.

WITH temp(company_id, item_number) AS (
SELECT cmp, item
FROM mylib.itembal
GROUP BY cmp, item
)
SELECT vendor, vendor_name, COUNT(a.item_number)
FROM mylib.vendor_items AS a,mylib.users AS b
JOIN temp AS t ON a.item_number = t.item_number AND
a.company_id = t.company_id
WHERE userid = ? AND a.web_site_id = b.web_site_id AND
a.company_id = 1
GROUP BY vendor, vendor_name
ORDER BY vendor_name

I'm sure this is how it's supposed to work, but I don't
really understand why. Is this standard SQL behavior?

The above rewritten more generically as:

SELECT ...
FROM V_I AS a
, USERS AS b JOIN TEMP AS t ON a.i = t.i

The above rewritten to match precedence for JOIN [over comma separated] with the parentheses explicitly specified [should seem odd; i.e. JOIN B to T using a column from A]:

Select ...
FROM V_I AS a
,( USERS AS b JOIN TEMP AS t ON a.i = t.i )

The revised and noted as functional request, for having moved USERS to the end in the joins, makes the query request JOIN A to T while using columns between those same two files:

Select ...
From ( V_I AS a
JOIN TEMP AS t ON a.i = t.i )
,USERS b

The above rewritten SELECT matches [nearly exactly; ignores the CTE and all clauses beyond the JOIN\ON] with the change barred scenario in the MTU. The original syntax and effect, prior to the error [error since 6.1] being issued, is an ambiguously requested [and implicitly performed on prior releases as a, for lack of JOIN and ON being specified between A and B] combination of CROSS JOIN and an INNER JOIN. Notice that the JOIN defines the relationship between B and T, yet the ON defines the relationship between A and B. Moving the B correlation to be the third file, the cross product is then no longer ambiguous for the implied order; i.e. the result is A INNER JOIN T CROSS JOIN B. AFaIK the combination of the CROSS and INNER should not matter for the result set, so apparently the stricter syntax requirements remain in effect irrespective the final result.? Prevent the error either by explicitly requesting the cross product join, or by rewriting the join criteria [JOIN & ON] to explicitly define how the joining should be accomplished without performing any cross product.

FWiW, the snippet from the .pdf of the MTU follows:

SQL0338 can also be issued when a table is specified followed by a comma and a joined table.

Select a.c1
From BASE1 a
, BASE2 b INNER JOIN BASE3 c on a.c1 = c.c1

is equivalent to

Select a.c1
From BASE1 a
, (BASE2 b INNER JOIN BASE3 c on a.c1 = c.c1)

To specify a.c1 in the ON clause for the joined table, include the first table within the joined table.

Select a.c1
From BASE1 a
CROSS JOIN BASE2 b
INNER JOIN BASE3 c
on a.c1 = c.c1

Regards, Chuck