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

x
Solved

# conditional where clause

Posted on 2011-03-08
Medium Priority
463 Views
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)
0
Question by:hipal

LVL 78

Accepted Solution

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

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

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
``````
0

LVL 32

Assisted Solution

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

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

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

ID: 35077858
subratabiswas,

How is that different from what Sharath_123 posted?

Please review previous posts before posting.
0

LVL 78

Assisted Solution

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

## Featured Post

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
###### Suggested Courses
Course of the Month12 days, 15 hours left to enroll