• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

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


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
cookiejar
Asked:
cookiejar
2 Solutions
 
dqmqCommented:
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
 
OP_ZaharinCommented:
- 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
 
cklautauCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now