Conditionals in SELECT statement

Dev Shed Forums Sponsor:

November 23rd, 2004, 02:08 PM

StevenC

PHP & Java Error Master

Join Date: Jun 2003

Location: My Computer

Posts: 1,218

Time spent in forums: 1 Day 1 h 32 m 40 sec
Reputation Power: 15

Conditionals in SELECT statement

Can you put conditionals in a SELECT statement?

I'm trying to do:

Code:

select
(
	IF (version_eol IS NOT NULL) THEN value='not null!'
	ELSE value='null!'
	END IF
) as value
FROM my.table;

But I get an error on it:

Code:

IF (version_eol IS NOT NULL) THEN value='null!'
                *
ERROR at line 3:
ORA-00907: missing right parenthesis

I'm new to PL/SQL. Any ideas?

__________________

Webinfractions.com
Think I'm wrong? You're probably right!

Last edited by StevenC : November 23rd, 2004 at 02:18 PM.

November 23rd, 2004, 02:21 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Munich, Bavaria

Posts: 989

Time spent in forums: 1 Week 3 Days 2 h 3 m 55 sec
Reputation Power: 57

SELECT decode(version_eol, null, 'null', 'not null')
as value
FROM my.table;

November 23rd, 2004, 02:25 PM

StevenC

PHP & Java Error Master

Join Date: Jun 2003

Location: My Computer

Posts: 1,218

Time spent in forums: 1 Day 1 h 32 m 40 sec
Reputation Power: 15

I probably shouldn't have used that example as I did.
I can't use decode, as version_eol is a date type.

I need to check if SYSDATE is greater than version_eol (checking on if something has expired).
From what I understand of decode, it can't handle comparisons like that.

November 23rd, 2004, 03:16 PM

hedge

Contributing User

Join Date: Apr 2002

Posts: 692

Time spent in forums: 4 Days 22 h 25 m 53 sec
Reputation Power: 19

This method is a little sneaky:

Code:

select decode(sign(version_eol-sysdate),-1,'less than sysdate',0,'equal to sysdate','greater than sysdate')
  from ??

November 23rd, 2004, 04:31 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,308

Time spent in forums: 1 Week 3 Days 5 h 21 sec
Reputation Power: 48

Is there some reason you cannot do this:

Code:

select fld1,fld2 from table where eol_version< sysdate;
/* or */
delete from table where eol_version< sysdate;

November 24th, 2004, 08:26 AM

StevenC

PHP & Java Error Master

Join Date: Jun 2003

Location: My Computer

Posts: 1,218

Time spent in forums: 1 Day 1 h 32 m 40 sec
Reputation Power: 15

Thanks hedge, that seems to be just want I wanted.

Jim: I can't use that select because I need to return a value based on each condition, hence the need for conditionals.
I don't want to return only some rows.
Also, why would I delete data from my table just to get to a few rows? That's not smart.

Last edited by StevenC : November 24th, 2004 at 08:30 AM.

November 24th, 2004, 09:35 AM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,308

Time spent in forums: 1 Week 3 Days 5 h 21 sec
Reputation Power: 48

You would not delete. I was just using the where clause as an example.

I know that you do what you want in SQL without that much of a problem with pretty standard SQL.

If you were to explain the data model, we coudl give you a good answer.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: