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?

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.

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