• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

conditional where clause

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
hipal
Asked:
hipal
7 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
mayankagarwalCommented:
select * from xyz where a=10 and b = 20 and(c is null or c = 30);
0
 
SharathData EngineerCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
awking00Commented:
To help clarify, can you post some sample data and the expected output?
0
 
subratabiswasCommented:
Select * from XYZ
    Where a = 10
        and b = 20
        and nvl(c, 30) = 30
0
 
subratabiswasCommented:
Sorry, I withdraw my submission; realized that the exact same solution is provided by Sharath_123
0
 
slightwv (䄆 Netminder) Commented:
subratabiswas,

How is that different from what Sharath_123 posted?

Please review previous posts before posting.
0
 
slightwv (䄆 Netminder) Commented:
You caught me typing...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now