Re: SQL0338 on 6.1

James

I must admit, the Memo to Users is rather confusing. The issue there is that the various JOIN sections do not have their respective ON statements right after them. From what I can tell, it wants you to organize how the files are to be joined. It says to use parentheses for that.

You also have a mix of the old, comma-separated-with-WHERE-to-set-JOIN structure and the newer JOIN structure. That can't help things. I'd try this - putting the a.web_site_id = b.web_site_id with its JOIN files - this makes the WHERE clause only for record selection, not for JOINing - separate the functions.

What this suggestion does - used to do, anyhow - is create a temp result for the first JOIN, based on its ON conditions. Then that result is JOINed to the 3rd file, using THAT ON condition. When the ON is not tied to its JOIN directly, there seems there isn't a way to tell exactly how to apply things. And different parenthetical arrangements can result in different results - this is a "black" science to me - have never quite worked it out. But it might have some nice capabilities, once a person learns how to leverage it.

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 JOIN mylib.users AS b
ON a.web_site_id = b.web_site_id
JOIN temp AS t ON a.item_number = t.item_number AND
a.company_id = t.company_id
WHERE userid = ? AND
a.company_id = 1
GROUP BY vendor, vendor_name
ORDER BY vendor_name

If you still get the error, try various parentheses - maybe around the first 2 files, including their ON condition. If you wanted to JOIN the 2nd and 3rd files first, then JOIN to the first one, you'd put parentheses around the 2nd and 3rd, with their ON condition, and then the ON condition for JOINing to the first at the end - I think - and that's where the example looks weird to me, in the Memo to Users.

HTH - and hope I'm even close!!
Vern

James Perkins wrote:

Hello All,
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?

Thanks in advance,
--
James R. Perkins
http://twitter.com/the_jamezp