Re: Strange SQL error

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

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.


On Fri, 2004-12-03 at 02:57, Peter.Colpaert@xxxxxxxxxx wrote:
> Hi group,
> 
> I hope this is the correct list (it is SQL intended to be used in an 
> SQLRPGLE program)
> 
> I'm trying to do a complex SQL Select, and I get an SQL0199 error "UNION 
> token not expected" when running it in JFaceDbc (or interactive SQL):
> 
> select atride01, dessho01, destec01, descom01, atrgrp01, atrcls01, 
> lower(dessho01), lower(destec01), lower(descom01) 
> from pdmdta/pdat01pf 
> where atride01 in
> (select c.atride07 from pdmdta/pdca07pf c
> where c.clside07 in 
> (SELECT distinct a.clside44
> FROM pdmdta/pdct44pf a 
> WHERE a.clscod44 = 'QA'
> UNION 
> SELECT b.clside44
> FROM pdmdta/pdct44pf a left outer join pdmdta/pdct44l0 b on a.clside44 = 
> b.clsidp44 
> WHERE a.clscod44 = 'QA' and b.clsidp44 <> ''
> UNION 
> SELECT b.clside44
> FROM pdmdta/pdct44pf a left outer join pdmdta/pdct44l0 b on a.clsidp44 = 
> b.clside44 
> WHERE a.clscod44 = 'QA' and  a.clsidp44 <> ''))
> 
> The strange part is that the lowermost select statement works, even with 
> the unions.  From the moment I use the result for a "where xxx in" clause, 
> the SQL no longer works.
> 
> I did manage to make it work by moving the UNIONS to the outermost select, 
> but that is not acceptable because I need to be able to order by any of 
> the fields from pdat01pf.
> 
> Can anyone tell me what I'm doing wrong, or how this can be made to work?
> 
> Thanks in advance.
> 
> Peter Colpaert
> Application Developer
> Massive - Kontich, Belgium
> -----
> Real developers never document.  If it was hard to write, it should be 
> hard to understand.
> -----
> --
> 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