Pitfalls of using CASE statements in the where clause

Dean Burchill

unread,

Jan 16, 1998, 3:00:00 AM1/16/98

to

---------------------- Forwarded by Dean J Burchill/TCC/Chrysler on
01/16/98 02:18 PM ---------------------------

Dean J Burchill
01/16/98 10:08 AM
To: Dorothy A Bennoune/VEO/Chrysler, Frances G Flater/VEO/Chrysler,
Mike A Gedz/TCC/Chrysler, Ann Marie O Gibson/TCC/Chrysler, CN=WAYNE_T
GRANT/dda.nickname=twg5 @ EMX, Sandra A Kleiss/TCC/Chrysler, Munther L
Shuwayhat/TCC/Chrysler, Curtis E Turner/TCC/Chrysler
cc: Edwin C McBride/TCC/Chrysler, Michelle D Silverman/TCC/Chrysler,
James L Frazer/TCC/Chrysler, LISTSERV @ LISTSERV.AMERICAN.EDU @ SMTP
Subject: Pitfalls of using CASE statements in the where clause

Be careful when using CASE statements in the WHERE clause of DB2 SQL
statements. They do not work exactly as you might think. I personally
think I've found a undocumented feature (bug) but here goes. In my
workings with the torque data to be downloaded to ODDC, I tried to use
the following SQL where clause using CASE statements:

EXEC SQL
SELECT
....
FROM
PRDT.TBTORQUE TQ,
PRDT.TBUSAGE4 U4
WHERE
TQ.TORQUE_IND = U4.TORQUE_IND
AND
(CASE
WHEN U4.MDL_YR = '96' THEN 0
WHEN U4.MDL_YR = '97' THEN O
ELSE
CASE
WHEN TQ.UNIT_MEAS = 'NR' THEN
CASE
WHEN TQ.RSDL_MIN IS NOT NULL THEN 0
.... /* OTHER CHECKS */
ELSE 1
END
WHEN TQ.UNIT_MEAS IN ('IN', 'FT', 'NM', 'DN') THEN
CASE
WHEN TQ.RSDL_MIN IS NULL THEN 0
.... /* OTHER CHECKS */
ELSE 1
END
ELSE 0
END
END) = 1

Unfortunately this did not work. I was still getting 1996 and 1997
model years in the result set. I tried many combinations of simple or
searched when clauses, but none worked. I finally tried the following:

EXEC SQL
SELECT
....
FROM
PRDT.TBTORQUE TQ,
PRDT.TBUSAGE4 U4
WHERE
TQ.TORQUE_IND = U4.TORQUE_IND
AND
(CASE
WHEN U4.MDL_YR = '96' THEN 0
WHEN U4.MDL_YR = '97' THEN O
ELSE 1) = 1 /* could also use regular NOT IN */
AND
(CASE
WHEN TQ.UNIT_MEAS = 'NR' THEN
CASE
WHEN TQ.RSDL_MIN IS NOT NULL THEN 0
.... /* OTHER CHECKS */
ELSE 1
END
WHEN TQ.UNIT_MEAS IN ('IN', 'FT', 'NM', 'DN') THEN
CASE
WHEN TQ.RSDL_MIN IS NULL THEN 0
.... /* OTHER CHECKS */
ELSE 1
END
ELSE 0
END) = 1

In subsequent other tests what I've seem to have discovered is that
CASE statements do not work as you think they might when mixing checks
from different tables. In other words in the first example that didn't
work, even though you might think that the value 0 should be set
whenever the model year field in TBUSAGE is '96' or '97', DB2 seems to
be evaluating each seperately and ignoring checks from all but one of
the tables. So even though the case statement for model year 96 and 97
should set a 0, the other checks for PRDT.TBTORQUE set a 1 and the rows
with '96' and '97' model years in the subsequent CASE statements.

The kicker to these mixing of tables in the CASE statement is that the
ELSE seems to work the last table specified in the list 'WHEN'
statements.

In a simpled version of the test, there are 391 rows in PRDT.TBTORQUE
that match with U4 (PRDT.TBUSAGE4) where the UNIT_MEAS = 'NR' and
MDL_YR '96' has 1, '97' has 10, '98' has 307, and '99' has 73. I tried
the following CASE statement:

TQ.TORQUE_IND = U4.TORQUE_IND
AND
(CASE
WHEN U4.MDL_YR = '96' THEN
0
WHEN U4.MDL_YR = '97' THEN
0
WHEN TQ.UNIT_MEAS = 'NR' THEN
1
ELSE
0
END) = 1

The result was all 391 rows (I think it should've been only 380 rows).
I changed the case statement as follows:

(CASE
WHEN TQ.UNIT_MEAS = 'NR' THEN
1
WHEN U4.MDL_YR = '96' THEN
0
WHEN U4.MDL_YR = '97' THEN
0
ELSE
0
END) = 1

The result was 0 rows. Therefore I am guessing that when mixing tables
in a
CASE or imbedded CASE statement, that DB2 is ignoring CASE statements
that have
a different table than the last table before the ELSE statement.

But if you add in the regular where clause
AND TQ.UNIT_MEAS = 'NR' then you now get all 391 rows.

This probably has to do with the consideration of:
If the CASE expression is in a select list or an IN predicate,
then
the search-condition in a searched-when-clause cannot be a quantified
predicate, an IN predicate, or an EXISTS
predicate.

So, for the interim, do not mix tables in case statements, to include
nested case statements.

I cannot find any documentation to support these 'problems'. We have
DB2 version 5 on our book manager manuals, but nothing seems to address
this and the documentation seems to indicate that the first (leftmost)
WHEN clause that evaluates to true should be used.

I am also sending this (with the help of Ed McBride) to a subscribed
group of DB2 users and experts. Hopefully they will have some insight
into this problem.

Dean

Harald Wilhelm

unread,

Jan 17, 1998, 3:00:00 AM1/17/98

to

On Fri, 16 Jan 1998 14:33:32 -0500, Dean Burchill wrote:

>WHEN U4.MDL_YR = '96' THEN 0
>WHEN U4.MDL_YR = '97' THEN O

>I was still getting 1996 and 1997 model years in the result set.

is this a typo (96/97 vs 1996/1997)?

____________________________________________________________________________
PGP Public Key Fingerprint: 79 5B DF 50 21 62 E6 28 C8 17 91 C7 C7 5B CD 22
Request PGP Key by sending an E-Mail to pgp...@hawi.de
Visit me at http://www.hawi.de
Harald (HaWi) Wilhelm

Michael Hannan

unread,

Jan 18, 1998, 3:00:00 AM1/18/98

to

Dean,

Thanks for the warnings re CASE usage. This is very usful info you have given.
It appears wise to restrict CASE statements to simple constructs using just
one table.

Harald was giving you a subtle warning re Year2000 Date compliance I think?

I would like to warn you about use of CASE in the WHERE clause from a
performance perspective.

While it looks like great fun to write procedural code almost like a program
in the WHERE clause, this not good SQL. Perhaps you were doing this just for
fun to try it out!
The old ways of using AND, OR, NOT, and brackets (parentheses) in the WHERE
clause is the way to go. If possible predicates should be made into 'Boolean
Term'. This means that they are ANDed with the rest of the predicates and
allow row rejection as soon as the boolean term is evaluated false. This may
save joining a row to the other table before rejecting it.
The other thing is to strive for boolean term predicates to be simple enough
to be stage 1 (all your predicates look like stage 1 capable if used
'normally') and better still indexable, so that unecessary rows are not
visited and unnecessary rows are not sent to the RDS (Relational Data System).

CASE has its place. That place is in the SELECT clause to avoid complex
expressions, and rarely in the WHERE clause to avoid very complex
expressions or to avoid recalculating expressions several times in the
predicates. I would limit usage to stage 2 predicates if at all possible.

From: Michael Hannan
(Just my personal opinions)
----------------------------
>From: Dean Burchill <db...@CHRYSLER.COM>

>Subject: Pitfalls of using CASE statements in the where clause

>01/16/98 02:18 PM ---------------------------
>

>Be careful when using CASE statements in the WHERE clause of DB2 SQL
>statements. They do not work exactly as you might think. I personally
>think I've found a undocumented feature (bug) but here goes. In my
>workings with the torque data to be downloaded to ODDC, I tried to use
>the following SQL where clause using CASE statements:
>
>EXEC SQL
> SELECT
> ....
> FROM
> PRDT.TBTORQUE TQ,
> PRDT.TBUSAGE4 U4
> WHERE
> TQ.TORQUE_IND = U4.TORQUE_IND
> AND
> (CASE

> WHEN U4.MDL_YR = '96' THEN 0
> WHEN U4.MDL_YR = '97' THEN O

> WHEN U4.MDL_YR = '96' THEN
> 0
> WHEN U4.MDL_YR = '97' THEN

> 0
> WHEN TQ.UNIT_MEAS = 'NR' THEN
> 1
> ELSE
> 0
> END) = 1
>
>The result was all 391 rows (I think it should've been only 380 rows).
>I changed the case statement as follows:
>
> (CASE
> WHEN TQ.UNIT_MEAS = 'NR' THEN
> 1

> WHEN U4.MDL_YR = '96' THEN
> 0
> WHEN U4.MDL_YR = '97' THEN

D Preble

unread,

Jan 18, 1998, 3:00:00 AM1/18/98

to Dean Burchill

I assume your real sql didn't the use of zero for 96 and letter o for 97.

Richard Yevich

unread,

Jan 20, 1998, 3:00:00 AM1/20/98

to

Dean and others,

There is a bug in DB2 with the case statement in WHERE clauses with JOINS
and it sometimes works and sometimes does not depending on columns in the
SELECT clause (yes, SELECT columns affect the output when there is a CASE
in the WHERE clause).

This works:
SELECT TABLE1.COLUMNS ONLY
FROM TABLE1, TABLE2
WHERE TABLE1 = TABLE2
AND (CASE CONDITIONS USING ONLY TABLE1)

This DOES NOT work:
SELECT TABLE1.COLUMNS, TABLE2.COLUMNS -- change here
FROM TABLE1, TABLE2
WHERE TABLE1 = TABLE2
AND (CASE CONDITIONS USING ONLY TABLE1)

This DOES NOT WORK:
SELECT TABLE1.COLUMNS ONLY
FROM TABLE1, TABLE2
WHERE TABLE1 = TABLE2
AND (CASE CONDITIONS USING ONLY TABLE2) -- change here

Actually there are more strange ways that CASE results in no rows being
returned. I have documented them all and forwarded them to IBM of course.
FOR THE MOMENT, I would AVOID CASE IN JOINS, etc. until this is resolved.

We have implemented CASE very successfully in WHERE clauses that do work,
and will stay with those.

Richard Yevich
--------------------------------
Information Technology Consulting, Data Modeling, Advanced Education
RYC Inc, USA (California, Florida, Illinois, Texas)
USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-305-361-6583
Web: http://www.ryci.com DB2 and Oracle VLDB Specialists

On Friday, January 16, 1998 2:34 PM, DB2 Data Base Discussion List

[SMTP:DB...@AMERICAN.EDU] On Behalf Of Dean Burchill wrote:
> ---------------------- Forwarded by Dean J Burchill/TCC/Chrysler on

> 01/16/98 02:18 PM ---------------------------
>
>

> Dean J Burchill
> 01/16/98 10:08 AM
> To: Dorothy A Bennoune/VEO/Chrysler, Frances G Flater/VEO/Chrysler,
> Mike A Gedz/TCC/Chrysler, Ann Marie O Gibson/TCC/Chrysler, CN=WAYNE_T
> GRANT/dda.nickname=twg5 @ EMX, Sandra A Kleiss/TCC/Chrysler, Munther L
> Shuwayhat/TCC/Chrysler, Curtis E Turner/TCC/Chrysler
> cc: Edwin C McBride/TCC/Chrysler, Michelle D Silverman/TCC/Chrysler,
> James L Frazer/TCC/Chrysler, LISTSERV @ LISTSERV.AMERICAN.EDU @ SMTP

> Subject: Pitfalls of using CASE statements in the where clause
>

> Be careful when using CASE statements in the WHERE clause of DB2 SQL
> statements. They do not work exactly as you might think. I personally
> think I've found a undocumented feature (bug) but here goes. In my
> workings with the torque data to be downloaded to ODDC, I tried to use
> the following SQL where clause using CASE statements:
>
> EXEC SQL
> SELECT
> ....
> FROM
> PRDT.TBTORQUE TQ,
> PRDT.TBUSAGE4 U4
> WHERE
> TQ.TORQUE_IND = U4.TORQUE_IND
> AND

> (CASE
> WHEN U4.MDL_YR = '96' THEN 0
> WHEN U4.MDL_YR = '97' THEN O
> ELSE

> (CASE
> WHEN U4.MDL_YR = '96' THEN 0
> WHEN U4.MDL_YR = '97' THEN O

> (CASE
> WHEN U4.MDL_YR = '96' THEN
> 0
> WHEN U4.MDL_YR = '97' THEN

> 0
> WHEN TQ.UNIT_MEAS = 'NR' THEN
> 1
> ELSE
> 0
> END) = 1
>
> The result was all 391 rows (I think it should've been only 380 rows).
> I changed the case statement as follows:
>
> (CASE
> WHEN TQ.UNIT_MEAS = 'NR' THEN
> 1

> WHEN U4.MDL_YR = '96' THEN
> 0
> WHEN U4.MDL_YR = '97' THEN