471,090 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

Statement returing True/False in decode() function

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.event_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
//
//
Jul 19 '05 #1
1 22959

Try this:

...
decode(sign(sysdate-event_start),-1,'N'
,decode(sign(event_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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by brainsucker | last post: by
3 posts views Thread by Mark Morton | last post: by
11 posts views Thread by Craig Francis | last post: by
4 posts views Thread by dkelly925 | last post: by
22 posts views Thread by Cogito | last post: by
9 posts views Thread by =?Utf-8?B?TWlrZQ==?= | last post: by
7 posts views Thread by =?Utf-8?B?Sm9zZXBo?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.