x
Solved

# using case statement in oracle where clause

Posted on 2010-11-17
Medium Priority
578 Views
experts, I need to use case statement in an oracle where clause. My requirement is that I have 3 inputs to a procedure i_mlg, i_term, i_dctbl which can have values or can have a -1 in them.
If it has  a -1 in any of them  for ex: if i_term has a -1 then I need to select all the terms in the table column. If it has a specified number say 2, then I need to select the value for the term which corresponds to 2 in the term column in the table. Similarly I need to do it for i_dctbl and i_mlg. how can I achieve this in a single select statement.?
0
Question by:chickanna
• 4
• 3
• 2
• +1

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 34154643
you can do like this:
``````WHERE ( col1 = i_mlg OR i_mlg = -1 )
AND ( col2 = i_term OR i_term = -1 )
AND ( col3 = i_dctbl OR i_dctbl = -1 )
``````
0

LVL 74

Assisted Solution

sdstuber earned 1000 total points
ID: 34154664
select * from your_table
where  1 = case when i_mlg = -1 then 1
when i_mlg = termcolumn then 1
else null
end

or don't use case at all

where ((i_mlg = -1)  or (i_mlg = termcolumn))

0

Author Comment

ID: 34155141
This is how my procedure is

procedure get_info (
i_code in number, i_term in number, i_mlg in number, i_dctbl in number)
IS
select code, term , mlg, dctbl
from table_a ta
where ta.code = i_code
-- code for which I want solution for is below in pseudo code ---
and (if i_mlg = -1 then select all rows for i_code which has values for i_mlg
if i_mlg = 100 then select rows for i_code where mlg = i_mlg )
and (if i_term = -1 then select all rows for i_code which has values for i_term
if i_term = 2 then select rows for i_code where term = i_term and ta)
and (if i_dctbl = -1 then select all rows for i_code which has values for i_dctbl
if i_dctbl = 5 then select rows for i_code where dctbl = i_dctbl)

I need to check all 3 input parameters for a -1 or a value and then do a select based on it.

table structure is below

create table table_a
(code number not null,
term number null,
mlg number null,
dctbl number null)

a single code can have multiple records of terms, mlg and dctbl.

Thanks
0

LVL 74

Expert Comment

ID: 34155263
select code, term , mlg, dctbl
from table_a ta
where ta.code = i_code
and ( i_mlg = -1  or ( i_mlg = 100 and mlg = i_mlg)  )
and ( i_term = -1 or ( i_term = 2 and term = i_term) )
and (i_dctbl = -1 or ( i_dctbl = 5 and dctbl = i_dctbl) )

0

LVL 143

Expert Comment

ID: 34155340
please see my first comment on how to do it.
0

Author Comment

ID: 34155423
hi sdstuber,
would the query work if i have a -1 as input in the parameters. I mean if there is a -1 in i_term
then I want to return all rows for i_code which has a term value and if there is a specific value in i_term- say 100 and it can range anywhere from 10 to 10000 (it can take 100, 110 ....) then i want to return only 1 row which satisfies for i_term and other conditions i_mlg and i_dctbl which has its own   -1 or a specific number check.

select code, term, mlg, dctbl
from table_a ta
where ta.code = i_code
and (i_mlg = -1  then would it return all rows for i_code which there is a term ???
or (mlg=i_mlg) would it return 1 row which qualifies the i_mlg value ??

Thanks.
0

LVL 74

Expert Comment

ID: 34155445
if you don't want to specify a particular value, then use the where clauses as indicated in the first 2 posts.

try the queries as we have suggested.  If they don't return the results you expect, pleast post examples that illustrate the problem
0

LVL 32

Expert Comment

ID: 34155553
See attached.
query.txt
0

Author Comment

ID: 34157931
angellll/sdstuber,
i have 2 new columns now in the table. the table is now
create table table_a
(code number not null,
term_min number null,
term_max number null,
mlg_min number null,
mlg_max number null,
dctbl number null)

if i have to now select the term and mlg between min and max if it has a specific value and the max value for term and mlg if there is a -1 in the inputs for term and mlg, how can I accomplish??

Thanks a lot for your solution.

0

Author Comment

ID: 34197147
Experts -- here is my table structure..
mlg_min number(20)
mlg_max number(20)
db_amt number(10,2)
term_min number(3)
term_max number(3)
age_max number(4)

my procedure inputs will have
my_proc(i_mlg number,i_db_amt number, term number, i_age_max number)

and the inputs can have these values
i_mlg can have a null, 9999 or a specific value
i_db_amt can have a null, 9999 or a specific value
term can have a null, 9999 or a specific value
i_age_max can have a null or a specific value

my requirement is
if there is a null in the values then don't consider the column for the id's
if there is a 9999 in the value then apply it to all records for the id's
if there is a specific value then apply it to the record that has the specific value. ex. for min and max columns if there is a value apply that value between the min and max values

The data in the table is attached in the file

ee-data.xls
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.