Solved

CASE statement in WHERE clause

Posted on 2006-10-22
5
12,609 Views
Last Modified: 2008-01-09
Can i write the following query:

SELECT a,b,c FROM my_table
WHERE    CASE in_emp_id = 0 AND date_from <> 0 THEN exh_valid_from = in_date_from
              , CASE in_emp_id <> 0 AND in_date_from <> 0 THEN exh_emp_id = in_emp_id
              , CASE in_emp_id <> 0 AND in_date_from <> 0 THEN exh_valid_from = in_date_from AND exh_emp_id = in_emp_id;

in_date_from and in_emp_id are input parameters passed to the procedure which contains this query.
exh_emp_id and exh_valid_from are dates from my_table.
Can someone help me out

Thanks
0
Comment
Question by:aaba646
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 19

Accepted Solution

by:
actonwang earned 168 total points
ID: 17786188
I think that what you want is like :

SELECT a,b,c FROM my_table
WHERE    (in_emp_id = 0 AND date_from <> 0 and exh_valid_from = in_date_from ) or
              (in_emp_id <> 0 AND in_date_from <> 0 and exh_emp_id = in_emp_id) or
              (CASE in_emp_id <> 0 AND in_date_from <> 0 and exh_valid_from = in_date_from AND exh_emp_id = in_emp_id)
/

right :)
Acton
0
 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 166 total points
ID: 17787295
I think You want

SELECT a,b,c
  FROM my_table
  WHERE (in_emp_id = 0 OR exh_emp_id = in_emp_id)
    AND (in_date_from = 0 OR xh_valid_from = in_date_from);
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 166 total points
ID: 17791548
Your 2nd & 3rd CASE conditions do not make sense since they evaluate the same conditions, but return diff. results : -

, CASE in_emp_id <> 0 AND in_date_from <> 0 THEN exh_emp_id = in_emp_id
, CASE in_emp_id <> 0 AND in_date_from <> 0 THEN exh_valid_from = in_date_from AND exh_emp_id = in_emp_id;

I suppose they shud be as below : -

  CASE in_emp_id = 0 AND in_date_from <> 0 THEN exh_valid_from = in_date_from
, CASE in_emp_id <> 0 AND in_date_from = 0 THEN exh_emp_id = in_emp_id
, CASE in_emp_id <> 0 AND in_date_from <> 0 THEN exh_valid_from = in_date_from AND exh_emp_id = in_emp_id;

If i understand ur requirement correctly, u have a procedure that has 2 IN parameters - in_emp_id, in_from_date
that u want to compare to ur table values.

Wat u need is as below : -

SELECT a, b, c FROM my_table
WHERE DECODE(in_emp_id,     0, exh_emp_id,     in_emp_id)     = exh_emp_id
AND     DECODE(in_date_from, 0, exh_valid_from, in_date_from) = exh_valid_from
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20296184
Forced accept.

Computer101
EE Admin
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

717 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