wood burning stoves 2.0*
The moose likes JDBC and the fly likes Condition based on case in where clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Condition based on case in where clause" Watch "Condition based on case in where clause" New topic
Author

Condition based on case in where clause

Nikhil Padharia
Ranch Hand

Joined: Apr 19, 2012
Posts: 37
Hello Ranchers,

I want to write a SQL giving results if present with one condition matched or else results with default value
for eg i have a table

I want result on based of Name as follows
Name = 'XYZ' --> result should contain vaules of id 2 and 4
Name = 'PQR' --> result should contain vaules of id 3

I tried writing


But this outputs all results which includes XYZ and DEFAULT which is correct as per the behaviour of decode

I want something like


Any suggestions how to write this.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

You seem to be on Oracle, in that case you could use the CASE expression, which is wordy, but easily readable: CASE expression.

However, I don't understand your requirements completely. Where does the initial value of the Name parameter come from?
Nikhil Padharia
Ranch Hand

Joined: Apr 19, 2012
Posts: 37
Martin Vajsar wrote:You seem to be on Oracle

Yes, I am on Oracle Database 11g

Martin Vajsar wrote:in that case you could use the CASE expression

CASE expression works same as decode with some more or less functionality

If in decode i say, i want to write like


for CASE it would be


But here it doesn't allow to write select count(Name) where Name='XYZ' = 0 saying missing expression

Martin Vajsar wrote:Where does the initial value of the Name parameter come from?

It comes dynamically whatever the user enters.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Nikhil Padharia wrote:Hello Ranchers,

I want to write a SQL giving results if present with one condition matched or else results with default value
for eg i have a table

I want result on based of Name as follows
Name = 'XYZ' --> result should contain vaules of id 2 and 4
Name = 'PQR' --> result should contain vaules of id 3

I tried writing


But this outputs all results which includes XYZ and DEFAULT which is correct as per the behaviour of decode

I want something like


Any suggestions how to write this.


Nikhil swapped your quote tags for code tags
so you want really


but you want to acheive this in 1 statement.

does this have to be database agnostic or are you working on 1 specific DB?



Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

bother Martin was a much quicker typer than me.

I am presuming you actually have a variable you want to check against and 'XYZ' is just an example you are using to test.

how about a having in the where clause



I haven't written a having for over 6 years so my syntax is a bit rusty
Nikhil Padharia
Ranch Hand

Joined: Apr 19, 2012
Posts: 37
Wendy Gibbons wrote:Nikhil swapped your quote tags for code tags

Thanks Wendy.

Yes, you correctly understood I want to achieve that in one statement.
And no database agonistic, I want it working on Oracle 11g
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Wendy Gibbons wrote:bother Martin was a much quicker typer than me.

Good wine takes its time to ripe, Wendy . The quick typing had already failed me at many occasions.

how about a having in the where clause...

I think that HAVING always needs a GROUP BY. You'd have to use a subquery: ... and (select count(*) from sample_table where name=?) = 0

But we're not interested in the number of rows, just whether a matching row exists, so we can do it this way:

However, I cannot convince myself to like the idea. I'd probably do that as two queries actually.
Nikhil Padharia
Ranch Hand

Joined: Apr 19, 2012
Posts: 37
Wendy Gibbons wrote:
how about a having in the where clause

Having takes aggregate_function thus writing select inside having gives error in SQL again complaining missing Expression
Nikhil Padharia
Ranch Hand

Joined: Apr 19, 2012
Posts: 37
Thanks a lot Martin, this helps.. Thanks to Wendy to for providing valuable sugesstions.

Martin Vajsar wrote:
However, I cannot convince myself to like the idea. I'd probably do that as two queries actually.

Yes, I agree to you. I wonder if there is any function which works for Oracle.


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Nikhil Padharia wrote:
Martin Vajsar wrote:
However, I cannot convince myself to like the idea. I'd probably do that as two queries actually.

Yes, I agree to you. I wonder if there is any function which works for Oracle.

Well, the above solution works. It is just very convoluted in my opinion. It is actually possible to express it using the CASE expression, and I'd say this makes it more clear what is happening:
This even seems to get better plan in the test case I've tried, but this would probably depend on your exact setup. But no, Oracle does not have a function which would allow to express this in a more concise manner.

I don't like the logic of this construct. This is like having Google return you results for the DEFAULT search term if the search terms you've entered didn't match anything on the web.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Martin Vajsar wrote:
Wendy Gibbons wrote:bother Martin was a much quicker typer than me.

Good wine takes its time to ripe, Wendy . The quick typing had already failed me at many occasions.

how about a having in the where clause...

I think that HAVING always needs a GROUP BY. You'd have to use a subquery: ... and (select count(*) from sample_table where name=?) = 0

But we're not interested in the number of rows, just whether a matching row exists, so we can do it this way:

However, I cannot convince myself to like the idea. I'd probably do that as two queries actually.


It all depends how complicated the real life select is, if it links to 10 other tables with outer joins (+) , then this little OR isn't that bad, but if the real code is as simple as this, i wouldn't do it this way.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Condition based on case in where clause
 
Similar Threads
DECODE function in Oracle gives duplicate data
Exception Handling
how to write select query when where condition contains ampersand symbol(&)?
Need help - Lot of joins are slowing down my query
how to put Nested conditions in sql query