Re: NEWBIE: conditional query

From: David Scott <dlscott_at_mindspring.com>
Date: 1996/11/29
Message-ID: <dlscott-2811962310470001_at_user-37bidfh.dialup.mindspring.com>#1/1


In article <01bbd669$c5abc760$ad3c29a6_at_wenker.con.mci.com>, "Peter Wenker" <peter.wenker_at_mci.com> wrote:

> I'm trying to select different values from a DATE field based upon the
> value of the date. Specifically, I want to query all rows in a table with
> a DATE field. I want the query to return a value of 'CLOSED' if the date
> is less than or equal to SYSDATE, and NULL if the date is greater than
> SYSDATE. Is this possible? I'm familiar with SQL*PLUS but not as
> familiar with its advanced functionality.
>

I would use:

SELECT
  DECODE( SIGN(yourdatefield - SYSDATE),1,NULL,'CLOSED') FROM
  yourtable
WHERE
  ...;

explanation:

Subtracting SYSDATE from yourdatefield will generate the following:

  • a POSITIVE number if the date is in the future
  • ZERO if the date is an EXACT match for time (seconds count)
  • a NEGATIVE number if the date is in the past. The SIGN function will return:
  • -1 if it is passed a NEGATIVE number
  • 0 if it is passed a ZERO
  • 1 if it is passed a POSITIVE number. DECODE is used to test the expression against a value of 1 (representing the future); if a "1" is found, a NULL will be returned. All other values (0 or -1) will return 'CLOSED' from the function call. The WHERE clause is left for your own purposes.

Hope this helps!


David Scott                        ...how can I keep from singing?
                bumper snicker: My other guitar is a Martin (D16H)
email: dlscott_at_mindspring.com
Received on Fri Nov 29 1996 - 00:00:00 CET

Original text of this message