p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Oracle (http://p2p.wrox.com/forumdisplay.php?f=105)
-   -   Case statement in Where (http://p2p.wrox.com/showthread.php?t=85714)

mnp13 November 17th, 2011 05:17 PM

Case statement in Where
 
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


All times are GMT -4. The time now is 01:41 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.