Home » SQL & PL/SQL » SQL & PL/SQL » Case statement in Where clause (Oracle 9i)
Case statement in Where clause [message #292376] Tue, 08 January 2008 18:02 Go to next message
sp@ml
Messages: 2
Registered: January 2008
Location: NJ
Junior Member
Here is a SQL Statement which is working and is very expencive in performance.

Select * from [Table1] Where
[some condition1] and
(([Operand_Parameter] = '=' and PROCESS_DATE = [Date_Parameter])
and
([Operand_Parameter] = '>' and PROCESS_DATE > [Date_Parameter])
and
([Operand_Parameter] = '<' and PROCESS_DATE < [Date_Parameter]))

We are talking about millions of records and "<" condition is killing. I need to optomize this query.

I tried to use Case statement in Where clause.

Select * from [Table1] Where
[some condition1] and
CASE [Operand_Parameter]
WHEN '=' THEN PROCESS_DATE = [Date_Parameter]
WHEN '>' THEN PROCESS_DATE > [Date_Parameter]
WHEN '<' THEN PROCESS_DATE < [Date_Parameter]
END

This is some syntax error. Please help and any other solution is welcome. Thanks in advance.
Re: Case statement in Where clause [message #292471 is a reply to message #292376] Wed, 09 January 2008 00:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Case doesn't work that way. Case is supposed to give a result on only one side of an equation.
What makes you think that using case would speed up your query? (which, by the way, is probably depicted incorrectly. I think you mean to use OR in your original query, instead of AND)
Re: Case statement in Where clause [message #292483 is a reply to message #292376] Wed, 09 January 2008 01:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Frank wanted to suggest you like ,

(Beware of Order of brackets )

Select * from [Table1] Where
[some condition1]
AND(([Operand_Parameter] = '=' AND  PROCESS_DATE = [Date_Parameter]) OR
    ([Operand_Parameter] = '>' AND  PROCESS_DATE > [Date_Parameter]) OR
    ([Operand_Parameter] = '<' AND  PROCESS_DATE < [Date_Parameter]));


Thumbs Up
Rajuvan.
Re: Case statement in Where clause [message #292653 is a reply to message #292483] Wed, 09 January 2008 09:18 Go to previous message
sp@ml
Messages: 2
Registered: January 2008
Location: NJ
Junior Member
Thanks Frank and rajavu1.
Previous Topic: Is it possible to know the code of any oracle functions
Next Topic: Dynamically Execute a Procedure
Goto Forum:
  


Current Time: Sun Dec 04 22:34:26 CST 2016

Total time taken to generate the page: 0.14205 seconds