RE: Simplify SQL Statement

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