Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 17th, 2011, 05:17 PM
Registered User
Points: 16, Level: 1
Points: 16, Level: 1 Points: 16, Level: 1 Points: 16, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2004
Location: , , .
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
  #2 (permalink)  
Old November 21st, 2011, 10:32 AM
Friend of Wrox
Points: 3,558, Level: 25
Points: 3,558, Level: 25 Points: 3,558, Level: 25 Points: 3,558, Level: 25
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: California, USA
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.
  #3 (permalink)  
Old December 14th, 2011, 07:07 AM
Registered User
Points: 9, Level: 1
Points: 9, Level: 1 Points: 9, Level: 1 Points: 9, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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.
  #4 (permalink)  
Old December 14th, 2011, 09:13 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
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/
  #5 (permalink)  
Old February 13th, 2012, 04:24 AM
Registered User
Points: 30, Level: 1
Points: 30, Level: 1 Points: 30, Level: 1 Points: 30, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
  #6 (permalink)  
Old March 8th, 2013, 07:27 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:08 PM.


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