Solved

using case statement in oracle where clause

Posted on 2010-11-17
10
512 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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 73

Assisted Solution

by:sdstuber
sdstuber earned 250 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 142

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get the parent node - XMLTYPE 9 73
Oracle 12c database link between pdb not working 20 76
Oracle SQL - Query help 7 54
Component is listed with a Protocol more than once 3 29
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 …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

777 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