?
Solved

using case statement in oracle where clause

Posted on 2010-11-17
10
Medium Priority
?
538 Views
Last Modified: 2013-12-18
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
Comment
Question by:chickanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 143

Accepted Solution

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

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
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

by:chickanna
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.

Please help!!!

Thanks
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 74

Expert Comment

by:sdstuber
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

by:Guy Hengel [angelIII / a3]
ID: 34155340
please see my first comment on how to do it.
0
 

Author Comment

by:chickanna
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.  

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
 
LVL 74

Expert Comment

by:sdstuber
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

by:awking00
ID: 34155553
See attached.
query.txt
0
 

Author Comment

by:chickanna
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

by:chickanna
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

Please help!!!!


ee-data.xls
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question