Re: CASE WHEN EXISTS Problem

[CRPence <CRPbottle@xxxxxxxxx>]

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