CODEWITHSUNDEEP
sqloracle-database
ahairshi
ahairshi

Reputation: 391

Oracle select query conditions

I have a search jsp, which has more than one criteria( ID OR NAME OR DATE OR ...) for searching the database. I need a single select query to satisfy all the condition. I have written the select query partially, but it is not working well. Please help me in finishing the query

   select * from table 1
   where Id in ( select id from table 1 where ( those three conditions here)

   i have (1=? or id=?)
         and (1=? or name=?)
         and (1=? or date =?)

but it returns the full table. I know those three condition returns true. How to modify that in order to obtain the result. I don't want any stored procedure, as i am a newbie

Upvotes: 0

Views: 271

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

You've got a redundant subquery.

I assume the first parameter will be 1 if the second parameter is null, and 0 if the second parameter is not null; likewise the 3rd parameter will 1 if the 4th parameter is null, and 0 if the 4th parameter is not null, etc.?

Try this:

select * from table
where (1=? or id=?)
     and (1=? or name=?)
     and (1=? or date =?)

For example, if the user was searching by name I'd expect the parameters to be something like (1,NULL,0,'SMITH',1,NULL).

(Also, you can't use 1 for the table alias.)

Upvotes: 0

Shamis Shukoor
Shamis Shukoor

Reputation: 2515

select * from table 1
   where Id in ( select 
         (case 
               when (1=? or id=?) and (1=? or name=? )  and (1=? or date =?) then id
          end) as id from table 1 )

I hope this is what you are looking for

Upvotes: 0

Bohemian
Bohemian

Reputation: 425358

If your conditions are true for every row, you'll get the full table.

For example, if your variable is 1, 1 = 1 will be true, and every row will be returned.


p,s, I don't understand why you've nested the select.

Upvotes: 1

Related Questions