All Forums Analytics
10 Sep 2008
Hiding a column from output

Hello!Consider the following SQL:SELECT CUST_ID,DTE_BIRTH,(DATE-DTE_BIRTH)/365.25 As AGE,CASEWHEN AGE < 18THEN 'Under 18'WHEN AGE BETWEEN 18 and 65THEN 'Between 18 and 65'WHEN AGE > 65THEN 'Over 65'Else 'Missing Data'EndFrom 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

Andrew Livingston EMEA - Customer Education Consultant Teradata UK 206 Marylebone Road London NW1 6LY United Kingdom M: +44 7785 971 080 E-mail: Andrew.Livingston@Teradata.com
Jim Chapman 449 posts Joined 09/04
10 Sep 2008

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.

Adeel Chaudhry 773 posts Joined 04/08
10 Sep 2008

Hello,You can also try following:SELECT CUST_ID,DTE_BIRTH,CASEWHEN (DATE-DTE_BIRTH)/365.25 < 18THEN 'Under 18'WHEN (DATE-DTE_BIRTH)/365.25 BETWEEN 18 and 65THEN 'Between 18 and 65'WHEN (DATE-DTE_BIRTH)/365.25 > 65THEN 'Over 65'Else 'Missing Data'EndFrom 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

-- If you are stuck at something .... consider it an opportunity to think anew.

Fred 1096 posts Joined 08/04
11 Sep 2008

Or if you don't mind using old-style Teradata extensions:SELECT CUST_ID,DTE_BIRTH,CASEWHEN (DATE-DTE_BIRTH)/365.25 (named AGE) < 18THEN 'Under 18'WHEN AGE BETWEEN 18 AND 65THEN 'Between 18 and 65'WHEN AGE > 65THEN 'Over 65'ELSE 'Missing Data'ENDFROM TABLE;

26 Sep 2008

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!

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

You must sign in to leave a comment.