Define a WITH query, derived table, or view containing the AGE derived column. You can then reference it in the case expression without including it in the select list of the main select.
Hello,
You can also try following:
SELECT CUST_ID
,DTE_BIRTH
,CASE
WHEN (DATE-DTE_BIRTH)/365.25 < 18
THEN 'Under 18'
WHEN (DATE-DTE_BIRTH)/365.25 BETWEEN 18 and 65
THEN 'Between 18 and 65'
WHEN (DATE-DTE_BIRTH)/365.25 > 65
THEN 'Over 65'
Else 'Missing Data'
End
From TABLE;
As far as performance (re-calculating same value on each WHEN) is concerned, atleast the explain is pretty identical in both cases. Though you should try it running on actual data.
HTH.
Regards,
Adeel
Or if you don't mind using old-style Teradata extensions:
SELECT CUST_ID
,DTE_BIRTH
,CASE
WHEN (DATE-DTE_BIRTH)/365.25 (named AGE) < 18
THEN 'Under 18'
WHEN AGE BETWEEN 18 AND 65
THEN 'Between 18 and 65'
WHEN AGE > 65
THEN 'Over 65'
ELSE 'Missing Data'
END
FROM TABLE;
Thanks everyone!
Adeel, thanks for your suggestion, I was trying to get around that so I could use Age instead of the formula.
Fred, I might just use the old Teradata extension in that way. Personally I think showing the actual age column is good so that you can prove the data.
Thanks for your suggestions!
Hello!
Consider the following SQL:
SELECT CUST_ID
,DTE_BIRTH
,(DATE-DTE_BIRTH)/365.25 As AGE
,CASE
WHEN AGE < 18
THEN 'Under 18'
WHEN AGE BETWEEN 18 and 65
THEN 'Between 18 and 65'
WHEN AGE > 65
THEN 'Over 65'
Else 'Missing Data'
End
From TABLE;
I've used the 3rd column only to be able to refer to it then in the CASE statement.
Is there anyway to suppress this column from the answerset?
Thanks!
Andrew