#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    3
    Rep Power
    0

    Decode questions


    I have oracle 8.1.7 and I'm trying to write a query that returns a 1 for valid data and a 0 for invalid data...

    I have a birth date field that is invalid if it is null, less than 16 years ago, or greater than 100 years ago. Right now my query is...

    SELECT
    DECODE (BirthDate,
    NULL, 0,
    1)
    FROM
    Person

    How can I include the range delimination?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26

    Re: Decode questions


    Originally posted by guesswho
    I have oracle 8.1.7 and I'm trying to write a query that returns a 1 for valid data and a 0 for invalid data...

    I have a birth date field that is invalid if it is null, less than 16 years ago, or greater than 100 years ago. Right now my query is...

    SELECT
    DECODE (BirthDate,
    NULL, 0,
    1)
    FROM
    Person

    How can I include the range delimination?
    you can't really do conditional statements in a decode, however using a combination of subtraction and checking the sign of the result it can be accomplished. however it is tough to understand later, I would probably write a function instead.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    The CASE expression is available in Oracle 8.1.7.
    The following should do what you want:


    SELECT
    CASE WHEN BirthDate IS NULL THEN NULL
    WHEN BirthDate - 16*365 < SYSDATE THEN NULL
    WHEN BirthDate - 100*365 > SYSDATE THEN NULL
    ELSE BirthDate
    END
    FROM
    Person

    Cheers,
    Dan

IMN logo majestic logo threadwatch logo seochat tools logo