CASE statement within WHERE clause

prgaj1 · Posted: Wed Mar 17, 2010 3:25 pm

Hi,

I need help to use CASE Statement within my WHERE Clause. I want to change the WHERE Clause based on my condition as following:

SELECT ...

FROM ...

WHERE (condition)

AND (condition)

AND (condition)

AND (
CASE WHEN IN_TYPE_FLAG = 'R'
THEN (REGNR_NA = 0) --- Problem
WHEN IN_TYPE_FLAG = 'S'
THEN (REGNR_NA <> 0) --- Problem
-- ELSE -- when in type flag = 'a'
END

)

In the above case, my WHERE Clause is dependent on one of the fields in a table and an input field. If the input field is 'R' then only records where the REGNR_NA is zeroes must be selected; if the input field is 'S' only records with REGNR_NA not equal zeroes must be selected otherwise if the input flag is 'A' then any records can be selected where REGNR_NA is zeroes or not.

I am getting error on the mentioned line (Problem Line). Since CASE is an expression and does not execute a statement, can anyone help me to get my WHERE Clause working... It is specify in my specs that I must use a CASE statement.

Thanks in advance...

[/b]

Srihari Gonugunta · Posted: Wed Mar 17, 2010 4:27 pm

Hi,
You may try this.

prgaj1 · Posted: Wed Mar 17, 2010 4:58 pm

Thank you very much - I agree it would have easy doing it as one usually do it but the SA (system analyst) wanted us to use a CASE statement... thanks again

dick scherrer · Posted: Wed Mar 17, 2010 8:38 pm

Hello,

GuyC · Posted: Thu Mar 18, 2010 7:48 pm

a criteria
REGNA_SZ = xxxxxxxxxxxxxxxxx
with xxxxxxxx being something that can be evaluated/calculated once
is Stage 1 and indexable.
most criteria with ORs are stage 2 and not indexable

So maybe the SA has a reason to ask for this.
a possiblilty is
REGNA_SZ between :hvmin and :hvmax
with (hvmin,hvmax) being (0,0), (1,999999) or (0,999999) depending on :type-flag