× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Instead of EXISTS() you can use another derived table and left outer join. 

-----Original Message-----
From: David Gibbs [mailto:david@xxxxxxxxxxxx]
Sent: Wednesday, June 08, 2005 10:39 AM
To: midrange-l@xxxxxxxxxxxx
Subject: 'CASE WHEN EXISTS' in SQL?


Folks:

A co-worker is trying to write a somewhat complex SQL statement and is
running into a problem.

The statement is:

----------
SELECT TypeID, Issues.ID, CreatedUser, CreatedDate, ModifiedUser,
ModifiedDate, Synopsis, StateID, Field3, Field4,
C1 Field7, CASE WHEN EXISTS(SELECT * FROM IIMap WHERE IIMap.Active=1 AND
FieldID=-12 AND IIMap.LeftID=Issues.ID) THEN 1 ELSE 0 END, C1 FROM Issues
LEFT JOIN (
  SELECT LeftID ID1, count(*) C1 FROM IIMap WHERE IIMap.Active = 1 AND
IIMap.FieldID = -12 GROUP BY LeftID
) J1 ON Issues.ID = J1.ID1 WHERE   (  (Issues.ID IN (SELECT LeftID FROM
IIMap JOIN Issues ON Issues.ID=IIMap.RightID WHERE IIMap.Active = 1 AND
IIMap.FieldID =
-12 AND (ProjectID IS NULL OR ProjectID IN(1,2))))  )   AND   (
((ProjectID IS
NULL OR ProjectID IN (1,2,0)))  )   AND   (  ((TypeID IN (2,1)))  )
ORDER BY Issues.ID
----------

gives the error:
 [SQL0104] Token EXISTS was not valid. Valid tokens: <IDENTIFIER>
<INTEGER> <CHARSTRING> <GRAPHSTRING>.

We assume its the EXISTS in the CASE WHEN clause.  This construct works
on all the other databases, including normal db2 (non 400).

Based on my review of the docs, it *SHOULD* work (I think).

Any thoughts?

Thanks!

david


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.