[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

using case statement in oracle where clause

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
chickanna
Asked:
chickanna
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 )

Open in new window

0
 
sdstuberCommented:
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
 
chickannaAuthor Commented:
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
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
sdstuberCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see my first comment on how to do it.
0
 
chickannaAuthor Commented:
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.
0
 
sdstuberCommented:
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
 
awking00Commented:
See attached.
query.txt
0
 
chickannaAuthor Commented:
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
 
chickannaAuthor Commented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now