RE: SQL UNION Problem

Hi,

UNION in a sub-select is not supported: 
But the following examples shout return the result you want:
1. Sub-Select und Exception Join
select Camvnr                                                     
   from    anvfiles/casrec exception join                          
          (      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)as x  
        on camvnr = camvn                                          

2. Common Table Expression
With x as (      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) 
select camvnr 
   from anvfiles/casrec exception join x on camvnr = camvn


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Pantzopoulos -
(H/O)
Sent: Thursday, October 26, 2006 02:57
To: midrange-l@xxxxxxxxxxxx
Subject: SQL UNION Problem

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

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