Rick -



I know nothing about the acceptability of a UNION in an EXISTS clause, but
can't you separate the two parts of the UNION as follows?:



.

WHERE (NOT EXISTS
(SELECT DATESBM FROM CLAIMSPAIDREVERSED WHERE RXCLAIMNBR='5' AND
CLMSEQNBR<=123 and CLAIMSTS='X'))

AND (NOT EXISTS (SELECT CHANGED FROM CLAIMAUDIT WHERE RXCLAIMNBR=5 AND
RXCLAIMSEQ=123 AND REASON='TEST'))



I doubt it would have much of an impact on performance - if any at all.



Louis Kleiman

SSTMS, Inc.



_____

From: Rick DeBay [mailto:rdebay@...]
Sent: Monday, July 12, 2004 9:37 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] UNION not allowed in EXISTS clause?



Does anyone know where I can start hunting for answers to this?

-----Original Message-----
From: Rick DeBay
Sent: Friday, July 09, 2004 11:53 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] UNION not allowed in EXISTS clause?

The select in the not-exists clause runs fine on its own. When it's
part of an exists or not-exists clause, it fails with the given error.

INSERT INTO CLAIMAUDIT (RXCLAIMNBR,RXCLAIMSEQ,AUDITSTATUS,REASON)
SELECT DISTINCT 5,123,0,'TEST'
FROM CLAIMAUDIT
WHERE NOT EXISTS
(SELECT DATESBM FROM CLAIMSPAIDREVERSED WHERE RXCLAIMNBR='5' AND
CLMSEQNBR<=123 and CLAIMSTS='X'
UNION
SELECT CHANGED FROM CLAIMAUDIT WHERE RXCLAIMNBR=5 AND
RXCLAIMSEQ=123 AND REASON='TEST')

Dynamic SQL Error SQL error code = -104 Token unknown - line 6, char 6
UNION

Rick DeBay
Senior Software Developer
RxStrategies.net





Yahoo! Groups Sponsor



ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=129688mu6/M=295196.4901138.6071305.3001176/D=gr
oups/S=1705115386:HM/EXP=1089725863/A=2128215/R=0/SIG=10se96mf6/*http:/compa
nion.yahoo.com> click here



<http://us.adserver.yahoo.com/l?M=295196.4901138.6071305.3001176/D=groups/S=
:HM/A=2128215/rand=315501135>



_____

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .



[Non-text portions of this message have been removed]