0

Want to create age buckets in Teradata 16.20.0.2; Database version: Teradata 16.20.53.56; Provider Version: Teradata.Net 16.20.1.0. Code throwing syntax error. Code:

select ptnt_bth_dt,
case 
     when ( cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date)) < 182 then '<6 mth'     
     when (cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date)) >= 182
       and (cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date)) <365 then '6-<12 mth'
     when (cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date)) >= 365
       and (cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date)) < 730 then '1-<2'
     when (cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365 >= 2
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 4 then '2-4'
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 5                   
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 11 then '5-11'            
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 12                    
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 17 then '12-17'            
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 18                    
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 29 then '18-29'            
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 30                    
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 39 then '30-39'            
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 40                    
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 49 then '40-49'            
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 50                    
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 64 then '50-64'            
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 65                    
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 74 then '65-74'            
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 75                    
       and round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) <= 84 then '75-84'            
     when round((cast(CURRENT_DATE as date) - cast(ptnt_bth_dt as date))/365) >= 85 then '85+'            
     else 'age missing' 
     end as 'Age_Groups'
     from 
     select 
     DATE '2022-12-30' AS ptnt_bth_dt

Error: "SELECT Failed. [3706] Syntax error: expected something between the word 'case ' and the 'when' keyword."

Tried different ways of converting to date or using elseif statements

Update: Modified the query to:

select t1.*,
case when t1.age_in_years between 2 and 4 then '2-4'
    when t1.age_in_years between 5 and 11 then '5-11'    
    when t1.age_in_years between 12 and 17 then '12-17'            
    when t1.age_in_years between 18 and 29 then '18-29'            
    when t1.age_in_years between 30 and 39 then '30-39'            
    when t1.age_in_years between 40 and 49 then '40-49'            
    when t1.age_in_years between 50 and 64 then '50-64'            
    when t1.age_in_years between 65 and 74 then '65-74'            
    when t1.age_in_years between 75 and 84 then '75-84'            
    when t1.age_in_years >= 85 then '85+'            
else 'NA' 
end as Age_Groups

from
(...)t1

Error:

SELECT Failed. [3707] Syntax error, expected something like an 'END' keyword between a string or a Unicode character literal and the word ' '.

Also (...)t1 inner query runs successfully

JNevill
  • 46,980
  • 4
  • 38
  • 63
Madhuri
  • 1
  • 3
  • No idea why you would receive that error, but the problem with the SQL posted is that the alias `Age_Groups` needs to be in double quotes `"` or no quotes, not single quotes, and the derived table expression in the FROM clause needs parentheses around it and an alias. In other words, the last few lines should be something like `end as Age_Groups from (select date'2022-12-30' as ptnt_bth_dt) as dt` – Fred Mar 22 '23 at 22:35
  • You don't need to CAST(... AS DATE) if it's already a DATE data type, like CURRENT_DATE. – Fred Mar 22 '23 at 22:56
  • You could also greatly simplify this by creating your date difference as a derived column with an alias, and then use that alias in your case statement, instead of repeating the calculation over and over and over. – Andrew Mar 23 '23 at 15:56
  • Thank you Fred and Andrew for your inputs. I tried incorporating both and now getting a different error. First created the age columns(age_in_days and age_in_years) and rectified the syntax error around the column name created by Case statement. – Madhuri Mar 23 '23 at 19:15
  • select t1.*,case when t1.age_in_years between 2 and 4 then '2-4' when t1.age_in_years between 5 and 11 then '5-11'    when t1.age_in_years between 12 and 17 then '12-17'            .......when t1.age_in_years >= 85 then '85+'            else 'NA' end as Age_Groups from (....) t1 – Madhuri Mar 23 '23 at 19:18
  • Now getting this error: "SELECT Failed. [3707] Syntax error, expected something like an 'END' keyword between a string or a Unicode character literal and the word ' '." The inner query (....) t1 runs successfully – Madhuri Mar 23 '23 at 19:18
  • Regarding your update, there is nothing syntactically wrong. I am able to run on my system without error. Using `FROM (SELECT 10 AS age_in_years FROM sys_calendar.CALENDAR WHERE calendar_date = CURRENT_DATE()) t1` – JNevill Mar 23 '23 at 19:41
  • ok, I tried with your "FROM (SELECT 10 AS age_in_years FROM sys_calendar.CALENDAR WHERE calendar_date = CURRENT_DATE()) t1" query and still throws me same error. After run, red error line comes below "'5-11' when". Could it be a version issue? – Madhuri Mar 23 '23 at 19:49
  • The most recent text you pasted above has "No-Break Space" special characters at the end of some lines including the one with '5-11'. Though they display as spaces, Teradata does not recognize them as valid whitespace within the text of a SQL statement. – Fred Mar 23 '23 at 22:35

0 Answers0