Solved

conditional where clause

Posted on 2011-03-08
8
445 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
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 72 total points
ID: 35071790
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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 71 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 142 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 142 total points
ID: 35077852
Sorry, I withdraw my submission; realized that the exact same solution is provided by Sharath_123
0
 
LVL 76

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 76

Assisted Solution

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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is an explanation of a simple data model to help parse a JSON feed
This is about my first experience with programming Arduino.

809 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