× 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.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.