Re: SQL Case Statement With Exists

IN and EXISTS are not supported syntax on the WHEN; see later in this post.

It seems the following SELECT with CASE expression could give the desired result, assuming distinct across the key relationships, or perhaps only by requesting a distinct result set:

select
case
when polrii IS NOT NULL then 'Y'
else 'N'
end as NEWFIELD
,A.*
from RSKR05 A
left outer join INTPTY B
on polbrr=polbri and poltyr=poltyi
and polnor=polnoi and pollor=polloi
and polrir=polrii

Or to achieve similar to the above [using SELECT DISTINCT]:

select 'Y' as NEWFIELD, A.*
from RSKR05 A
where exists
(select 1 from INTPTY
where polbrr=polbri and poltyr=poltyi
and polnor=polnoi and pollor=polloi
and polrir=polrii )
UNION
select 'N' as NEWFIELD, A.*
from RSKR05 A
where not exists
(select 0 from INTPTY
where polbrr=polbri and poltyr=poltyi
and polnor=polnoi and pollor=polloi
and polrir=polrii )


Searching InfoCenter for /search condition sql/ seemed not to mention any restriction, although it was only discussed in context of WHERE and HAVING. Searches seemed only to give CASE STATEMENT for its syntax. However with further searching, I did finally find CASE EXPRESSION:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/db2/rbafzmstdatetimearith.htm
For the above link, find and position to _CASE expressions_ where the following is described for WHEN--search-condition for the CASE--+-searched-when-clause

*search-condition*
Specifies a condition that is true, false, or unknown about a row or group of table data.
The search-condition must *not* include a subquery in an EXISTS or IN predicate.

Regards, Chuck