Solved

conditional where clause

Posted on 2011-03-08
8
443 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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 143 total points
Comment Utility
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 72 total points
Comment Utility
select * from xyz where a=10 and b = 20 and(c is null or c = 30);
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 72 total points
Comment Utility
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 71 total points
Comment Utility
To help clarify, can you post some sample data and the expected output?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Assisted Solution

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

Assisted Solution

by:subratabiswas
subratabiswas earned 142 total points
Comment Utility
Sorry, I withdraw my submission; realized that the exact same solution is provided by Sharath_123
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
subratabiswas,

How is that different from what Sharath_123 posted?

Please review previous posts before posting.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 143 total points
Comment Utility
You caught me typing...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Via a live example, show how to take different types of Oracle backups using RMAN.
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 …

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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now