RE: Simplify SQL Statement

["ibm" <ibm@xxxxxxxxxx>]

Thank you.  I will practice this.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, November 25, 2009 7:17 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Simplify SQL Statement

ibm wrote:
> In this query I am creating a computed "HOLD FLAG" column. Can I
> use this computed column in evaluating the "ON-HOLD COMMENTS"
> column, or do I have to run the sub-select query like I am doing
> now?
>
> <code>
>
>   select
>     (CASE
>        WHEN
>              (SELECT COUNT(*) FROM AMFLIB.MBCKREP
>               WHERE AMFLIB.MBCKREP.CKCVNB = AMFLIB.MBCDREP.CDCVNB
>                 AND AMFLIB.MBCKREP.CKBCDT > 0
>                 AND AMFLIB.MBCKREP.CKBDDT = 0 ) > 0
>        THEN 1
>        ELSE 0
>      END) AS "HOLD FLAG"
>    ,(CASE
>        WHEN
>              (SELECT COUNT(*) FROM AMFLIB.MBCKREP
>               WHERE AMFLIB.MBCKREP.CKCVNB = AMFLIB.MBCDREP.CDCVNB
>                 AND AMFLIB.MBCKREP.CKBCDT > 0
>                 AND AMFLIB.MBCKREP.CKBDDT = 0 ) > 0
>        THEN 'UNCONTROLLED DELAY'
>        WHEN
>              (SELECT COUNT(*) FROM AMFLIB.MBCKREP
>               WHERE AMFLIB.MBCKREP.CKCVNB = AMFLIB.MBCDREP.CDCVNB
>                 AND AMFLIB.MBCKREP.CKBCDT > 0
>                 AND AMFLIB.MBCKREP.CKBDDT = 0 ) = 0
>          AND AMFLIB.MBC6REP.C6UUCA = ' '
>        THEN '_________________' 
>        WHEN
>             (SELECT COUNT(*) FROM AMFLIB.MBCKREP 
>              WHERE AMFLIB.MBCKREP.CKCVNB = AMFLIB.MBCDREP.CDCVNB
>                AND AMFLIB.MBCKREP.CKBCDT > 0
>                AND AMFLIB.MBCKREP.CKBDDT = 0 ) = 0
>          AND AMFLIB.MBC6REP.C6UUCA = 'X'
>        THEN '*UNCONTROLLED DELAY' 
>        ELSE '_________________'  END) AS "ON-HOLD COMMENTS"
>    , ...
>   from AMFLIB.MBC6REP
>    ...
>
> </code>

   The /computed column/ is actually an /expression/ whose value 
would need to be in a table [function] against which to join, so the 
value is in an actual column.  The repeated SELECT logic could be 
reduced in size [but not in number] using a CTE, then the repeated 
SELECT logic is done instead against the one-row CTE.  I can post an 
example if requested, but...

   However by clever use of both the SIGN function to establish 
equal & greater than zero evaluated as zero & one respectively *and* 
with a cartesian product join of the file being queried with that 
own-row result table, the query can become much simpler:

<code>

  WITH
    TmpHld (Hld) AS
     /* COUNT(*) > 0 := 1; COUNT(*) = 0 := 0 */
     (SELECT SIGN(COUNT(*)) AS "HOLD FLAG"
       FROM AMFLIB.MBCKREP
       WHERE AMFLIB.MBCKREP.CKCVNB = AMFLIB.MBCDREP.CDCVNB
         AND AMFLIB.MBCKREP.CKBCDT > 0
         AND AMFLIB.MBCKREP.CKBDDT = 0 )
  select
    TmpHld.Hld AS "HOLD FLAG"
   ,(CASE WHEN TmpHld.Hld = 1
          THEN 'UNCONTROLLED DELAY'
          WHEN TmpHld.Hld = 0
          AND AMFLIB.MBC6REP.C6UUCA = ' '
          THEN '_________________'
          WHEN TmpHld.Hld = 0
          AND AMFLIB.MBC6REP.C6UUCA = 'X'
          THEN '*UNCONTROLLED DELAY'
          ELSE '_________________'
     END) AS "ON-HOLD COMMENTS"
   , ...
   from       AMFLIB.MBC6REP
   cross join TmpHld
   ...

</code>

Regards, Chuck