Search code examples
t-sqlssms-2014

unable to access case statement output from outer query.

I am trying to execute the following query in SSMS 2014. However, I am unable to access from the outer query, the column created using a case statement in the inner query. i.e.I am not able to access c.current. Error obtained - Incorrect syntax near C

select C.trandate,C.plan,C.current from
(SELECT d.trandate,p.plan,
 case when datediff(dd,trandate,getdate()) <=30 then d.amount else 0 end as  'Current',
 case when datediff(dd,trandate,getdate()) between 31 and 60 then d.amount else 0 end as '31 to 60',
 case when datediff(dd,trandate,getdate()) between 331 and 360 then d.amount else 0 end as '331 to 360',
 case when datediff(dd,trandate,getdate()) > 360 then d.amount else 0 end as '>360',d.residentsys
FROM [HMXals_Reporting].[dbo].[TranARDetail] d
join [HMXals_Reporting].[dbo].[plans] p
on d.transys = p.plansys

) C

Solution

  • plan and current are both reserved keywords.
    You'll have to go with
    select C.trandate, C.[plan], C.[current] from
    or choose other names.