Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

conditional where clause

Posted on 2011-03-08
8
Medium Priority
?
463 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
8 Comments
 
LVL 78

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 78

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 78

Assisted Solution

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Loops Section Overview

971 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