CODEWITHSUNDEEP
javasqloracle-databasejdbc
Vinay
Vinay

Reputation: 739

Changing where clause criteria based on some condition

Task I am trying to achieve: I have a query whose where clause condition is to be updated dynamically based on one condition keeping it in a normal programming language below is what I am trying to achieve

considering "name" is a column on which I am checking the condition

If(name==""){
  select * from infodata where col1='somevalue' and col2='somevalue'
} else if(name=="2"){
  select * from infodata where col3='somevalue' or col4='somevalue'
} else {
  select * from infodata where col2='somevalue' and col5='somevalue'
}

based on that value I need to assign different where conditions. (I am using Oracle as the DB)

Currently I am achieving this is by writing the conditions in the Java code to assign respective where condition to the query

what i am looking for is a way to do it in a sql query so that i can pass the required data in the prepared statement. Just wanted to know is this can be possible using single SQL query or not, if possible could you please provide an example or syntax how to achieve this.

Thanks.

Upvotes: 0

Views: 993

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

It sounds like you could simply combine the conditions

select *
  from infodata
 where (name is null and col1 = 'somevalue' and col2 = 'somevalue')
    or (name = '2'   and (col3 = 'somevalue' or  col4 = 'somevalue'))
    or (name != '2'  and col2 = 'somevalue' and col5 = 'somevalue)

Remember that Oracle does not have the concept of an empty string that is separate from NULL so I'm assuming that you would want to check whether the name IS NULL in your first condition.

Upvotes: 1

Related Questions