?
Solved

oracle 10g case when in a where clause using a variable for the condition

Posted on 2011-05-06
3
Medium Priority
?
539 Views
Last Modified: 2012-05-11

I would like to evaluate on a variable that being passed to a stored procedure to determine which condition in the case when clause to perform.
The variable in this example is
vPrimary.  If vPrimary = Y' get value where dept_id = 1 else get value where department_name like 'MERCHANT%'
I am not sure this is the correct construct because I have never used a case when in a where clause

select
   name,
   department
from table1,
     table2,
where table1.emp_id = table2.emp_id
  and case when vPrimary = 'Y'
           dept_id = '1'
     else department like 'MERCHANT%'
0
Comment
Question by:cookiejar
3 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 1000 total points
ID: 35709876
select
   name,
   department
from table1
inner join table2 on table1.emp_id=table2.emp_id
where
(vPrimary = 'Y' and dept_id = '1')
or
(vPrimary <> 'Y' and department like 'MERCHANT%')
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 1000 total points
ID: 35710478
- this is not tested but you might want to use it this way:

vPrimary VARCHAR(1) := Prival;

BEGIN
SELECT
   name,
   department
FROM table1
INNER JOIN table2 ON table1.emp_id = table2.emp_id
WHERE
    CASE WHEN vPrimary = 'Y' AND dept_id = '1' then 1
    CASE WHEN vPrimary = 'N' AND department like 'MERCHANT%' then 1
END = 1
0
 
LVL 3

Expert Comment

by:cklautau
ID: 35712035
If you are doing it thru a procedure I suggest to use a simple if and write two specific queries according to the value of vPrimary.
It keeps the code simple and it is better than using the case.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
Suggested Courses

831 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