RE: CASE WHEN EXISTS Problem

[Tom E Stieger <testieger@xxxxxxxxxxxx>]

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.