Re: Strange SQL error

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

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