Re: SQL0338 on 6.1

Thanks Vern,
That does make sense on why it's doing it, but I don't really understand why
it HAS to do it. Not that it really matters thought :-)

I did get it to work by placing the "comma join" at the end of the
statement. I see where the groupings need to be now.

I don't generally do a "comma join", I just inherited it and haven't yet
changed since it's never been a problem.

I appreciate the long well thought out answer.

--
James R. Perkins
http://twitter.com/the_jamezp


On Wed, Sep 16, 2009 at 13:10, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.