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

Posted on 2011-05-06
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

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

    Accepted Solution

    from table1
    inner join table2 on table1.emp_id=table2.emp_id
    (vPrimary = 'Y' and dept_id = '1')
    (vPrimary <> 'Y' and department like 'MERCHANT%')
    LVL 23

    Assisted Solution

    - this is not tested but you might want to use it this way:

    vPrimary VARCHAR(1) := Prival;

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

    Expert Comment

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    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.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    728 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

    17 Experts available now in Live!

    Get 1:1 Help Now