Thanks, Chuck. I hadn't seen it in the documentation. It's good to know it is in there and I won't have to worry about it just being me.
-Tom
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Saturday, March 19, 2011 9:04 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: CASE WHEN EXISTS Problem
On 3/18/11 4:02 PM, Tom E Stieger wrote:
<<SNIP>> I still am not sure why the EXISTS predicate doesn't seem to
work.
I am not sure of "why" beyond that the documentation suggests the "CASE expression" does not allow the EXISTS, thus the -104 seems appropriate, according to [and unchanged in v7r1]:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcaseexp.htm
"searched-when-clause
Specifies a search-condition that is applied to each
row or group of table data presented for evaluation,
and the result when that condition is true."
<<SNIP>>
"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.
There are two scalar functions, NULLIF and COALESCE,
that are specialized to handle a subset of the
functionality provided by CASE. The following table
shows the equivalent expressions using CASE or these
functions.
Table 1. Equivalent CASE Expressions
CASE Expression Equivalent Expr
CASE WHEN e1=e2 THEN NULL ELSE e1 END NULLIF(e1,e2)
CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END COALESCE(e1,e2)
..."
The DB2 for z [v10] suggests a more recent [change bars] less restrictive usage:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/db2z_caseexpression.htm
"search-condition
Specifies a condition that is true, false, or unknown
about a row or group of table data.
The search-condition can be a predicate, including
predicates that contain fullselects
(scalar or non-scalar) or row-value expressions.
If search-condition in a searched-when-clause specifies a
quantified predicate or an IN predicate that includes a
fullselect, the CASE expression cannot be used in the
following contexts:
* select lists
* a VALUES clause of an INSERT or MERGE statement
* a SET or assignment clause of an UPDATE, MERGE, or DELETE statement
* the right side of a SET or assignment statement
* the definition of a column mask
If search-condition in a searched-when-clause specifies
an EXISTS predicate, the CASE expression cannot be used
in the following contexts:
* a VALUES clause of an INSERT or MERGE statement
* the right side of a SET or assignment statement"
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
