?
Solved

conditional where clause

Posted on 2011-03-08
8
Medium Priority
?
457 Views
Last Modified: 2012-05-11
Hi,

I want to write something like this

Select * from XYZ
Where
a = 10
b=20
if(c!= null)
 and c = 30

(if c is null then I do not want any condition on 'c' in my where clause)
Please help !!
0
Comment
Question by:hipal
[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
8 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 572 total points
ID: 35071775
If 'c = 30' in the where clause it cannot be null so no need for the check.

I figure you've tried to simplify the question but think you might have over simplified it.  Can you provide a little more detail?

Are you asking that if a 'paramter' for c is not provided, then do not check column c in the where?

Typically you need dynamic SQL for this but I think you can do it with someting like this, ssuming 30 is a paramter value and not hardcoded:

select * from XYZ
where a=10 and b=20 and c = case when 30 is null then 30 else c end;
0
 
LVL 5

Assisted Solution

by:mayankagarwal
mayankagarwal earned 288 total points
ID: 35071790
select * from xyz where a=10 and b = 20 and(c is null or c = 30);
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 288 total points
ID: 35072307
Check if you want AND or OR operation between the filters.
SELECT * 
  FROM XYZ 
 WHERE a = 10 
       AND b = 20 
       AND NVL(c,30) = 30

Open in new window

0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 284 total points
ID: 35073029
To help clarify, can you post some sample data and the expected output?
0
 
LVL 4

Assisted Solution

by:subratabiswas
subratabiswas earned 568 total points
ID: 35077830
Select * from XYZ
    Where a = 10
        and b = 20
        and nvl(c, 30) = 30
0
 
LVL 4

Assisted Solution

by:subratabiswas
subratabiswas earned 568 total points
ID: 35077852
Sorry, I withdraw my submission; realized that the exact same solution is provided by Sharath_123
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35077858
subratabiswas,

How is that different from what Sharath_123 posted?

Please review previous posts before posting.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 572 total points
ID: 35077864
You caught me typing...
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
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 recover a database from a user managed backup

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