Re: Strange SQL error

Aha, now I see it, it must have been something that trivial (that I didn't 
see of course).

Thanks for showing me the light.

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:50
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


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.