RE: 'CASE WHEN EXISTS' in SQL?

["phil Kestenbaum" <pkestenbaum@xxxxxxxxx>]

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