Problems with CASE statement

[Sean C.]

Helpful folks,

I seem to be misunderstanding the use of the CASE statement as used
within a WHERE clause. I had thought that one could do the following
to prioritize possible results, but the example below returns two
rows, where I would expect it to return one:

CREATE TABLE DB2ADMIN.SPCTEMP (
COL1 CHAR(6)
)
IN USERSPACE1 NOT LOGGED INITIALLY;
COMMIT;
INSERT INTO SPCTEMP VALUES ('ABC');
INSERT INTO SPCTEMP VALUES ('XYZ');


SELECT * FROM SPCTEMP
WHERE
1 =
CASE
WHEN COL1 = 'ABC' THEN 1
WHEN COL1 = 'XYZ' THEN 1
ELSE 0
END
;

The intent is, if there are rows which contain both 'abc' and 'xyz',
then I only want to return the rows with 'abc'. if there are no 'abc'
rows, then I'll take the 'xyz' rows. My understanding of the CASE
statement is that it ends when it encounters the first TRUE condition,
but this example seems to 'fall through' to the following WHEN even if
the first WHEN evaluates to true. What am I missing?
Any help would be appreciated.

[Daniel Morgan]

"Sean C." wrote:

> Helpful folks,
>
> I seem to be misunderstanding the use of the CASE statement as used
> within a WHERE clause. I had thought that one could do the following
> to prioritize possible results, but the example below returns two
> rows, where I would expect it to return one:
>
> CREATE TABLE DB2ADMIN.SPCTEMP (
> COL1 CHAR(6)
> )
> IN USERSPACE1 NOT LOGGED INITIALLY;
> COMMIT;
> INSERT INTO SPCTEMP VALUES ('ABC');
> INSERT INTO SPCTEMP VALUES ('XYZ');
>
> SELECT * FROM SPCTEMP
> WHERE
> 1 =
> CASE
> WHEN COL1 = 'ABC' THEN 1
> WHEN COL1 = 'XYZ' THEN 1
> ELSE 0
> END
> ;
>
> The intent is, if there are rows which contain both 'abc' and 'xyz',
> then I only want to return the rows with 'abc'. if there are no 'abc'
> rows, then I'll take the 'xyz' rows. My understanding of the CASE
> statement is that it ends when it encounters the first TRUE condition,
> but this example seems to 'fall through' to the following WHEN even if
> the first WHEN evaluates to true. What am I missing?
> Any help would be appreciated.

Seems to me the CASE statement is the last thing you should be using. Try:

SELECT * FROM SPCTEMP
WHERE col1 IN ('ABC','XYZ');

--
Daniel Morgan
[url]http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp[/url]
[url]http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp[/url]
[email]damorgan@x.washington.edu[/email]
(replace 'x' with a 'u' to reply)

[Tokunaga T.]

[email]dba_fla@yahoo.com[/email] (Sean C.) wrote in message news:<2f9c2d92.0308281123.5ce4c28c@posting.google. com>...

> Helpful folks,
>
> I seem to be misunderstanding the use of the CASE statement as used
> within a WHERE clause. I had thought that one could do the following
> to prioritize possible results, but the example below returns two
> rows, where I would expect it to return one:
>
> CREATE TABLE DB2ADMIN.SPCTEMP (
> COL1 CHAR(6)
> )
> IN USERSPACE1 NOT LOGGED INITIALLY;
> COMMIT;
> INSERT INTO SPCTEMP VALUES ('ABC');
> INSERT INTO SPCTEMP VALUES ('XYZ');
>
>
> SELECT * FROM SPCTEMP
> WHERE
> 1 =
> CASE
> WHEN COL1 = 'ABC' THEN 1
> WHEN COL1 = 'XYZ' THEN 1
> ELSE 0
> END
> ;
>
> The intent is, if there are rows which contain both 'abc' and 'xyz',
> then I only want to return the rows with 'abc'. if there are no 'abc'
> rows, then I'll take the 'xyz' rows. My understanding of the CASE
> statement is that it ends when it encounters the first TRUE condition,
> but this example seems to 'fall through' to the following WHEN even if
> the first WHEN evaluates to true. What am I missing?
> Any help would be appreciated.

Predicates in a WHERE clause are evaluated for each rows.
In your example of CASE expression:
For 1st row(VALUES ('ABC')), it will end after evaluation of first condition.
For 2nd row(VALUES ('XYZ')), it will end after evaluation of second condition.
Hence, both rows will be selected.

For your requirement, how about this way?
SELECT * FROM SPCTEMP
WHERE COL1 = 'ABC'
OR
COL1 = 'XYZ'
AND
NOT EXISTS (SELECT * FROM SPCTEMP
WHERE COL1 = 'ABC'
)
;

[Sean C.]

Thanks for the feedback guys.
@Daniel, the example I posted was as simplistic as I could devise,
just to show the behavior I was observing. My requirements are far
more complex, and the query I was trying to construct is given below.
In a nutshell, the situation is that we have an 'Override' table
(DATAFIX) that is used to convert or override the values on incoming
order data. The criteria used to determine if an override should occur
is to match on 4 distinct column values coming in on the order. For
simplicity, I've labelled them DATA1 through DATA4. The design of the
DATAFIX 'override' table is that rows can exist in this table that can
match on all four column values, or on only one, or any combination.
The application must find the most 'granular' match and use that row
to supply override values. That is, if an order has values in all four
DATA columns, and there is a row on the DATAFIX table that has values
that match the first two DATA columns and another row that matches on
all four DATA columns, I want to select the latter row.
I thought I could implement this 'most granular to least granular'
prioritizing by using the 'exit on first true condition' nature of the
CASE statement. So, the WHEN clauses in the CASE statement below are
ordered in most granular to least granular order.

Evidently, as Tokunaga T mentioned, I cannot use that method in a
WHERE clause. If anyone can provide a more elegant solution I would be
most appreciative.

Thanks,
Sean


select D.DATA1, D.DATA2, D.DATA3, D.DATA4, O.DATA1, O.DATA2, O.DATA3,
O.DATA4
from DATAFIX D, ORDERS O

where O.orderid = 99999999
and D.SEARCHCODE = O.SEARCHCODE

and (D.DATA1 is not null or D.DATA2 is not null or D.DATA3 is not null
or D.DATA4 is not null)
and (D.DATA1 = O.DATA1)

and(
CASE

WHEN (O.DATA2 IS NOT NULL AND D.DATA3 IS NOT NULL AND O.DATA4 IS
NOT NULL
AND D.DATA2 = O.DATA2 AND D.DATA3 = O.DATA3 AND D.DATA4 =
O.DATA4) THEN 1

WHEN (O.DATA2 IS NOT NULL AND D.DATA3 IS NOT NULL AND O.DATA4 IS
NULL
AND D.DATA2 = O.DATA2 AND D.DATA3 = O.DATA3) THEN 1

WHEN (O.DATA2 IS NULL AND O.DATA3 IS NOT NULL AND O.DATA4 IS NOT
NULL
AND D.DATA3 = O.DATA3 AND D.DATA4 = O.DATA4) THEN 1

WHEN (O.DATA2 IS NOT NULL AND O.DATA3 IS NULL AND O.DATA4 IS NOT
NULL
AND D.DATA2 = O.DATA2 AND D.DATA4 = O.DATA4) THEN 1

WHEN (O.DATA2 IS NOT NULL AND D.DATA2 = O.DATA2) THEN 1

WHEN (O.DATA3 IS NOT NULL AND D.DATA3 = O.DATA3) THEN 1

WHEN (O.DATA4 IS NOT NULL AND D.DATA4 = '*') THEN 1

WHEN (O.DATA4 IS NOT NULL AND D.DATA4 = O.DATA4) THEN 1

ELSE 0
END) = 1

[andreyp#Antispam@mapsitnA#e-vision-group.com]

CREATE TABLE DB2ADMIN.SPCTEMP (
COL1 CHAR(6)
)
INSERT INTO SPCTEMP VALUES ('ABC');
INSERT INTO SPCTEMP VALUES ('XYZ');


I think result values will be 'ABC ', 'XYZ '. Correct use with func
like RTRIM, LEFT etc (but You lost index use) or add space to variables or
migrate to varchar.


Andy

[Lennart Jonsson]

[email]dba_fla@yahoo.com[/email] (Sean C.) wrote in message news:<2f9c2d92.0308290550.12eb580b@posting.google. com>...

In a bit of a hurry, but I'll give some suggestions below

[...]

>
> select D.DATA1, D.DATA2, D.DATA3, D.DATA4, O.DATA1, O.DATA2, O.DATA3,
> O.DATA4
> from DATAFIX D, ORDERS O
>
> where O.orderid = 99999999
> and D.SEARCHCODE = O.SEARCHCODE
>
> and (D.DATA1 is not null or D.DATA2 is not null or D.DATA3 is not null
> or D.DATA4 is not null)

Not sure, but perhaps this can be expressed as

coalesce(D.DATA1,D.DATA2,D.DATA3,D.DATA4) is not null and

> and (D.DATA1 = O.DATA1)
>
> and(
> CASE
>
> WHEN (O.DATA2 IS NOT NULL AND D.DATA3 IS NOT NULL AND O.DATA4 IS
> NOT NULL
> AND D.DATA2 = O.DATA2 AND D.DATA3 = O.DATA3 AND D.DATA4 =
> O.DATA4) THEN 1
>

I beleive the IS NOT NULL is redundant since D.DATAx = O.DATAx can
never evaluate to true as long as any of them IS NULL (NULL = NULL ->
false). Thus this becomes:

CASE WHEN (
D.DATA2 = O.DATA2 AND
D.DATA3 = O.DATA3 AND
D.DATA4 = O.DATA4
) THEN 1

In a similar fashion you can handle the rest of the cases.

My next step would be to try to reduce the number of cases. Check if
some of your cases are redundant of another one. You could try
something like:

with data (x) as (values 'x', 'y', cast(null as char(1))) select d1.x,
d2.x, d3.x, d4.x, o1.x, o2.x, o3.x, o4.x, case ...., case .... from
data d1, data d2, data d3, data d4, data o1, data o2, data o3, data o4

and play around with them to see if any one subsumes another one

HTH
/Lennart

[...]

[Lennart Jonsson]

[email]lennart@kommunicera.umea.se[/email] (Lennart Jonsson) wrote in message news:<6dae7e65.0308300401.2808466b@posting.google. com>...

> [email]dba_fla@yahoo.com[/email] (Sean C.) wrote in message news:<2f9c2d92.0308290550.12eb580b@posting.google. com>...
>

[...]

Perhaps I have oversimplified, but does this do the trick?

where
O.orderid = 99999999 and
D.SEARCHCODE = O.SEARCHCODE and
D.DATA1 = O.DATA1 AND
(
D.DATA2 = O.DATA2 OR
D.DATA3 = O.DATA3 OR
D.DATA4 = O.DATA4 OR
D.DATA4 = '*'
)

/Lennart

[Tokunaga T.]

[email]dba_fla@yahoo.com[/email] (Sean C.) wrote in message news:<2f9c2d92.0308290550.12eb580b@posting.google. com>...

> Thanks for the feedback guys.
> @Daniel, the example I posted was as simplistic as I could devise,
> just to show the behavior I was observing. My requirements are far
> more complex, and the query I was trying to construct is given below.
> In a nutshell, the situation is that we have an 'Override' table
> (DATAFIX) that is used to convert or override the values on incoming
> order data. The criteria used to determine if an override should occur
> is to match on 4 distinct column values coming in on the order. For
> simplicity, I've labelled them DATA1 through DATA4. The design of the
> DATAFIX 'override' table is that rows can exist in this table that can
> match on all four column values, or on only one, or any combination.
> The application must find the most 'granular' match and use that row
> to supply override values. That is, if an order has values in all four
> DATA columns, and there is a row on the DATAFIX table that has values
> that match the first two DATA columns and another row that matches on
> all four DATA columns, I want to select the latter row.
> I thought I could implement this 'most granular to least granular'
> prioritizing by using the 'exit on first true condition' nature of the
> CASE statement. So, the WHEN clauses in the CASE statement below are
> ordered in most granular to least granular order.
>
> Evidently, as Tokunaga T mentioned, I cannot use that method in a
> WHERE clause. If anyone can provide a more elegant solution I would be
> most appreciative.
>
> Thanks,
> Sean
>

Though not so elegant, but how about this?

SELECT D.DATA1, D.DATA2, D.DATA3, D.DATA4, O.DATA1, O.DATA2, O.DATA3, O.DATA4
FROM ORDERS O
, TABLE (SELECT D.DATA1, D.DATA2, D.DATA3, D.DATA4,
CASE
WHEN ( D.DATA2 = O.DATA2
AND D.DATA3 = O.DATA3
AND D.DATA4 = O.DATA4 ) THEN 1
WHEN ( O.DATA4 IS NULL
AND D.DATA2 = O.DATA2
AND D.DATA3 = O.DATA3 ) THEN 2
WHEN ( O.DATA2 IS NULL
AND D.DATA3 = O.DATA3
AND D.DATA4 = O.DATA4 ) THEN 3
WHEN ( O.DATA3 IS NULL
AND D.DATA2 = O.DATA2
AND D.DATA4 = O.DATA4 ) THEN 4
WHEN ( O.DATA3 IS NULL
AND O.DATA4 IS NULL
AND D.DATA2 = O.DATA2 ) THEN 5
WHEN ( O.DATA2 IS NULL
AND O.DATA4 IS NULL
AND D.DATA3 = O.DATA3 ) THEN 6
WHEN ( O.DATA2 IS NULL
AND O.DATA3 IS NULL
AND D.DATA4 = '*' ) THEN 7
WHEN ( O.DATA2 IS NULL
AND O.DATA3 IS NULL
AND D.DATA4 = O.DATA4 ) THEN 8
ELSE 9999
END AS Priority
FROM DATAFIX D
WHERE D.SEARCHCODE = O.SEARCHCODE
AND D.DATA1 = O.DATA1
AND (
( D.DATA2 = O.DATA2
AND D.DATA3 = O.DATA3
AND D.DATA4 = O.DATA4 )
OR ( O.DATA4 IS NULL
AND D.DATA2 = O.DATA2
AND D.DATA3 = O.DATA3 )
OR ( O.DATA2 IS NULL
AND D.DATA3 = O.DATA3
AND D.DATA4 = O.DATA4 )
OR ( O.DATA3 IS NULL
AND D.DATA2 = O.DATA2
AND D.DATA4 = O.DATA4 )
OR ( O.DATA3 IS NULL
AND O.DATA4 IS NULL
AND D.DATA2 = O.DATA2 )
OR ( O.DATA2 IS NULL
AND O.DATA4 IS NULL
AND D.DATA3 = O.DATA3 )
OR ( O.DATA2 IS NULL
AND O.DATA3 IS NULL
AND D.DATA4 = '*' )
OR ( O.DATA2 IS NULL
AND O.DATA3 IS NULL
AND D.DATA4 = O.DATA4 )
)
ORDER BY Priority ASC
FETCH FRIST 1 ROW ONLY
) AS D
WHERE O.orderid = 99999999
;

(I think "DATAx IS NULL"s in a CASE expression in a subquery can be removed.
But I'm not sure.)