Decode Function Inside Select

[LeighDreyer]

Tryint to check if a persons age is over 65 if so place a Y in table else place a N in table this is my attempts with no luck it is perfomed inside a SELECT statement and not an update. line with satrs is where I need to perform DECODE

SELECT
A.NAME AS "WARD NAME",
F.TODAYS_DATE AS "DATE",
DECODE(C.SEXXX_REFNO,'250564','M','250416', 'F') AS "SEX",
***ROUND((F.TODAYS_DATE - C.DATE_OF_BIRTH) / 365) AS "AGE"

Method 1
DECODE(ROUND((F.TODAYS_DATE - C.DATE_OF_BIRTH) / 365) > 65 ,Y,N) AS "AGE"

Method 2
Decode(Sign(65-ROUND((F.TODAYS_DATE - C.DATE_OF_BIRTH) / 365)), 1, N, Y )

anyone know how to do this ?

 

[GrSw]

How about using the CASE statement:

SELECT retired =
       CASE 
         WHEN DATEDIFF(YEAR, DATE_OF_BIRTH, TODAYS_DATE) > 65 THEN 'Y'
         ELSE 'N'
       END
FROM ...

Hope this helps
Grzegorz

 

[JamesLean]

Are you using SQL Server? I've never heard of DECODE() and can't find any reference to it in BOL.

PS, don't double-post! This query is already being dealt with in thread183-497337 --James

 

[LeighDreyer]

Hi

I am Using ORACLE james have used it succesfully for the previous line of code but it seems to give me an error when doing it with age problem

 

[JamesLean]

I suggest you post this question in the Oracle forum - this is the SQL Server forum and this function doesn't exist in SQL Server! --James