Case statement in Where

mnp13 · November 17th, 2011, 05:17 PM

I need to compare dates in my where clause, and based on the result, decided what statement to use.

Here it is in normal English:

SELECT DATA
FROM TABLE
WHERE
IF date1 = date2
THEN USE date3 > date4 for the select
ELSE USE date1 > date2 for the select

I tried CASE, but I'm not doing something right.

Any help would be appreciated!!

Thanks,

Michelle

melvik · November 21st, 2011, 10:32 AM

u should use CASE or SubQuery

Squire11 · December 14th, 2011, 07:07 AM

Hi, I have a form with 2 fields. one is a date, the other a varchar2.
Now a user can either select 1 of the 2 or both of the 2. I want to
submit the users' selection as a query. If only 1 filed is selected,
then the proper column/field in the db will be compared against that
field. If both fields are selected, I need to use an AND statement so
that both fields from the database will be compared against what the
user submitted.
I was thinking of using a case statement in the where clause, but
unsure if this is the best approach.

joefawcett · December 14th, 2011, 09:13 AM

I'm no expert in Oracle but in SQL Server's version of SQL you'd have something like:

Code:

SELECT * FROM MyTable
WHERE
(DateField = @StartDate OR @StartDate IS NULL)
AND (TextField = @TextInput OR @TextInput IS NULL)

This way you compare if the two inputs (which start with the @ sign) exist and not if they are left as NULL.

akaas.website · February 13th, 2012, 04:24 AM

try this:

SELECT DATA
FROM TABLE
WHERE
1 = (
CASE
WHEN date1 = date2 THEN
CASE
WHEN date3 > date4 THEN 1
ELSE 0
END
ELSE
CASE
WHEN date1 > date2 THEN 1
ELSE 0
END
END
)

Regards,
Akaas Developer
http://www.questions-interviews.com/...es/oracle.aspx

pragyaware · March 8th, 2013, 07:27 AM

You can use case statement regarding this issue, plz check a program below:-

SELECT CASE (selector)
CASE (label-list-1)
statements-1
CASE (label-list-2)
statements-2
CASE (label-list-3)
statements-3
.............
CASE (label-list-n)
statements-n
CASE DEFAULT
statements-DEFAULT
END SELECT