using case statement in oracle where clause

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.

Please help!!!

Thanks

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) )

please see my first comment on how to do it.

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.  

so, would your query

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.

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

See attached.
query.txt

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.


 

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

Please help!!!!


ee-data.xls