RE: Strange SQL error

[CWilt@xxxxxxxxxxxx]

No, the optimizer doesn't rewrite the Union as a Join.

The results are not the same between the two operations.

The reason it works this way is that the full-select statement, which
contains the UNION, is used as a common table expression.

An equivalent statement is:
      c/exec sql  
      c+  with table1 as 
      c+ ( Select fdvdcd as vendor,  
      c+         vdvnam as vdname,  
      c+         rfslip as slip  
      c+  From rflog, fldmst, isfile, vdmast  
      c+  Where (rfuser = :k1user) and  
      c+        (rftype = :type) and  
      c+        date(rftrdt) = date(:k1date) and  
      c+        isslip = rfslip and  
      c+        fdflno = isflno and  
      c+        fdlnno = isflln and  
      c+        vdvdcd = fdvdcd  
      c+  Union  
      c+  Select fdvdcd as vendor,  
      c+         vdvnam as vdname,  
      c+         rfslip as slip  
      c+  From rflog, fldmst, bofile, vdmast  
      c+  Where (rfuser = :k1user) and  
      c+        (rftype = :type) and  
      c+        rftype <> 'BI' and  
      c+        date(rftrdt) = date(:k1date) and  
      c+        boslip = rfslip and  
      c+        fdflno = boflno and  
      c+        fdlnno = boflln and  
      c+        vdvdcd = fdvdcd  
      c+       )         
      c+ Declare CR1 cursor for  
      c+  Select vendor,  
      c+         vdname,  
      c+         count(slip)  
      c+  From table1
      c+  Group by vendor,  
      c+           vdname  
      c+  Order by vendor,  
      c+           vdname  

This alternate syntax makes it easy to see that a temporary table is being
built that can be used in the later select statement(s).

Note: I don't use CTE in embedded in SQL.  So, the location of the declare
might not be correct.  It may need to go at the top, but I don't think so.  

Also, if the CTE above is used often, but not often enough to create a view.
Consider putting it into a UDF table function.  That would allow the
following:
      c+ Declare CR1 cursor for  
      c+  Select vendor,  
      c+         vdname,  
      c+         count(slip)  
      c+  From table(myUDFtable)
      c+  Group by vendor,  
      c+           vdname  
      c+  Order by vendor,  
      c+           vdname  

HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: R Bruce Hoffman [mailto:bruce.hoffman@xxxxxxxxxxxxxxxxx]
> Sent: Friday, December 03, 2004 7:51 AM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: Strange SQL error
> 
> 
> That's a FROM clause, not a WHERE clause. The optimizer (IIRC) will
> rewrite the union as a join and then provide the result to the select.
> 
> 
> On Fri, 2004-12-03 at 07:43, Peter.Colpaert@xxxxxxxxxx wrote:
> > What I don't understand, is that the following statement does work:
> > 
> >      c/exec sql  
> >      c+ Declare CR1 cursor for  
> >      c+  Select vendor,  
> >      c+         vdname,  
> >      c+         count(slip)  
> >      c+  From (  
> >      c+  Select fdvdcd as vendor,  
> >      c+         vdvnam as vdname,  
> >      c+         rfslip as slip  
> >      c+  From rflog, fldmst, isfile, vdmast  
> >      c+  Where (rfuser = :k1user) and  
> >      c+        (rftype = :type) and  
> >      c+        date(rftrdt) = date(:k1date) and  
> >      c+        isslip = rfslip and  
> >      c+        fdflno = isflno and  
> >      c+        fdlnno = isflln and  
> >      c+        vdvdcd = fdvdcd  
> >      c+  Union  
> >      c+  Select fdvdcd as vendor,  
> >      c+         vdvnam as vdname,  
> >      c+         rfslip as slip  
> >      c+  From rflog, fldmst, bofile, vdmast  
> >      c+  Where (rfuser = :k1user) and  
> >      c+        (rftype = :type) and  
> >      c+        rftype <> 'BI' and  
> >      c+        date(rftrdt) = date(:k1date) and  
> >      c+        boslip = rfslip and  
> >      c+        fdflno = boflno and  
> >      c+        fdlnno = boflln and  
> >      c+        vdvdcd = fdvdcd  
> >      c+       ) as table1  
> >      c+  Group by vendor,  
> >      c+           vdname  
> >      c+  Order by vendor,  
> >      c+           vdname  
> >      c/end-exec  
> >  
> > 
> > The difference might be that I'm now at a new client on 
> V5R1M0, and the 
> > above was made on V5R3M0...
> > 
> > Or is this something completely different?
> > 
> > Thanks,
> > 
> > Peter Colpaert
> > Application Developer
> > Massive - Kontich, Belgium
> > -----
> > Real developers never document.  If it was hard to write, 
> it should be 
> > hard to understand.
> > -----
> > 
> > 
> > 
> > 
> > 
> > R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>
> > Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> > 03/12/2004 13:36
> > Please respond to RPG programming on the AS400 / iSeries
> > 
> >  
> >         To:     RPG programming on the AS400 / iSeries 
> <rpg400-l@xxxxxxxxxxxx>
> >         cc: 
> >         Subject:        Re: Strange SQL error
> > 
> > 
> > Well, if I'm not mistaken, selects in a where clause must be
> > sub-selects.  By definition, any select with a union operator is a 
> > full-select.
> > Part of the language definition.
> > 
> > 
> > 
> > 
> > --
> > This is the RPG programming on the AS400 / iSeries 
> (RPG400-L) mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> -- 
> "Bigamy is having one wife too many. Monogamy is the same."
> -- Oscar Wilde
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>