Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 17th, 2011, 05:17 PM
Registered User
 
Join Date: Jul 2004
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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
 
Old November 21st, 2011, 10:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default

u should use CASE or SubQuery
__________________
Always,
Hovik Melkomian.
 
Old December 14th, 2011, 07:07 AM
Registered User
 
Join Date: Dec 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old December 14th, 2011, 09:13 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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.
__________________
Joe
http://joe.fawcett.name/
 
Old February 13th, 2012, 04:24 AM
Registered User
 
Join Date: May 2011
Posts: 6
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old March 8th, 2013, 07:27 AM
Registered User
 
Join Date: Mar 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Case Statement eastwest SQL Server 2005 8 August 6th, 2009 05:19 PM
Case statement cole SQL Language 3 May 8th, 2005 03:02 PM
case statement Hudson40 Access VBA 1 February 11th, 2005 11:31 AM
Using A CASE Statement fastcorvette Access 5 December 24th, 2003 01:39 PM
case statement jakeone Beginning PHP 10 August 19th, 2003 03:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.