SQL UNION Problem

["Mike Pantzopoulos - \(H/O\)" <mpantzopoulos@xxxxxxxxxxxxxxxx>]

I have an SQL I'm having a problem with. It might simply be that UNION
is not permitted in a sub select.

 

When I request the following SQL, it works correctly:

 

SELECT  CAMVND as camvn 

FROM anvfiles/casdeb 

UNION 

SELECT CAMVNC as camvn

FROM anvfiles/casclm 

UNION 

SELECT CAMVND as camvn 

FROM anvfiles/casrid 

UNION 

SELECT CAMVNG as camvn 

FROM anvfiles/casgnl

 

When I include the above in a sub-select as follows, it fails with 

 

Token UNION was not valid. Valid tokens: ).                       

 

 

select camvnr 

from anvfiles/casrec 

where camvnr not in(

SELECT  CAMVND as camvn 

FROM anvfiles/casdeb 

UNION 

SELECT CAMVNC as camvn 

FROM anvfiles/casclm 

UNION 

SELECT CAMVND as camvn 

FROM anvfiles/casrid 

UNION 

SELECT CAMVNG as camvn FROM anvfiles/casgnl)

 

 

Is a UNION permitted in a sub-select?

 

Thanks, 

 

Mike Pantzopoulos

 


*********************************************************************************************************
This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. If 
you are not the intended recipient, any use, disclosure or copying of this 
message is unauthorised. If you have received this message in error, please 
reply using the sender's email address. 

This footnote confirms that this email message has been scanned for computer 
viruses. EIG-Ansvar Limited does not accept liability for any loss or damage, 
whether caused by our own negligence or not, that results from a computer virus 
or defect in the transmission of this email or any attached file. 

EIG-Ansvar Limited - Australia (A.B.N. 21 007 216 506)
Email : insure@xxxxxxxxxxxxxxxx

Eig-Ansvar Limited - New Zealand
Email : insure@xxxxxxxxxxxxxxxx

*********************************************************************************************************