?
Solved

conditional where clause

Posted on 2011-03-08
8
Medium Priority
?
465 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Simple Linear Regression

621 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