|
I think the Declare must happen before the "with" definitions.... Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: CWilt@xxxxxxxxxxxx [mailto:CWilt@xxxxxxxxxxxx] Sent: Friday, December 03, 2004 7:44 AM To: rpg400-l@xxxxxxxxxxxx Subject: RE: Strange SQL error 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. > -- 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
