Strange SQL error

[Peter.Colpaert@xxxxxxxxxx]

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.
-----