Statement returing True/False in decode() function

[Walt]

Hi,

I want to write a query that returns Y or N depending on whether the
current time is within a specified window i.e.

EVENT_TABLE
----------
event_id number PK
event_start date
event_end date

I tried this:
SELECT event_id,
decode( sysdate between event_start and event_end, TRUE, 'Y',
'N')
FROM event_table

Unfortunately, Oracle won't accept a boolean expression inside the
decode function.

I've accomplished what I want via a correlated sub-query:

SELECT event_id,
nvl((SELECT 'Y' from event_table e2
WHERE sysdate between event_start and event_end
AND e.event_id=e2.e vent_id),'N')
FROM event_table e

But I'm wondering if there's a more aesthetic way to do it. Is there a
way to feed an expression that evaluates to true-or-false to the first
argument of the decode function?

Thanks.

--
//-Walt
//
//

[LKBrwn_DBA]

Re: Statement returing True/False in decode() function


Try this:

...
decode(sign(sys date-event_start),-1,'N'
,decode(sign(ev ent_end-sysdate),-1,'N','Y'))
...

also you can use:
...
case
when sysdate between event_start and event_end
then 'Y' else 'N'
end as date_flag
...

--
Posted via http://dbforums.com