Re: SQL0338 on 6.1

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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
>