Solved

using case statement in oracle where clause

Posted on 2010-11-17
10
504 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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]
Comment Utility
please see my first comment on how to do it.
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.

 

Author Comment

by:chickanna
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
See attached.
query.txt
0
 

Author Comment

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

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now