Receiving this error: The multi-part identifier "c.hrmort" could not be bound

Question

All solutions point to the fact that if I've assigned aliases in my code, I should be using them instead of the table name itself. I have assigned alias and I AM trying to use the alias, however, I am still receiving this error.

Does this have to do with using an left join?

My code is as follows:

Select 
    x.MrtCategory
    
        from 
            (select case 
            when c.hrmort='CMHC' then 'CMHC'
            when c.hrmort='Genworth' then 'Genworth'
            when c.hrmort=''
                and a.purp in ('P16','P17') 
                and c.tenure in ('Freehold','Condo','Strata')
                and a.secval<1000000
                and a.amorty<=25
                and a.class in ('Standard','Stf Benefit Rate','Stf Member Rate')
                and a.totltov<80
            then 'Conventional Insurable'
            when c.hrmort IS NULL then 'Other'
            else 'Conventional UnInsurable'
            end as MrtCategory,
    sum(a.amount) as 'Amount'

from 
    ODS_WB.dbo.lnap as a left join ODS_WB.dbo.cust as b on a.no_=b.no_ and a.surname=b.surname
    left join ODS_WB.dbo.scur as c on b.rowno=c.rowno_custscur_cust and a.secval=c.secvalue and c.status='active'

where 
    year(a.appdate)=2020 and month(a.appdate)=6 and a.apptype='Mortgage' and a.sourcecode in ('FI',' ')) as x

group by 
    c.hrmort

Answer 1

Your group by is outside of the subquery, so c.hrmort doesn't exist. Instead group by x.MrtCategory and sum the amount outside of the subquery:

Select x.MrtCategory,
       sum(x.amount) as Amount
from (
    select
        case 
          when c.hrmort='CMHC' then 'CMHC'
          when c.hrmort='Genworth' then 'Genworth'
          when c.hrmort=''
            and a.purp in ('P16','P17') 
            and c.tenure in ('Freehold','Condo','Strata')
            and a.secval<1000000
            and a.amorty<=25
            and a.class in ('Standard','Stf Benefit Rate','Stf Member Rate')
            and a.totltov<80
            then 'Conventional Insurable'
          when c.hrmort IS NULL then 'Other'
          else 'Conventional UnInsurable'
        end as MrtCategory,
--        sum(a.amount) as 'Amount'
        a.amount
    from      ODS_WB.dbo.lnap as a
    left join ODS_WB.dbo.cust as b on a.no_=b.no_ and a.surname=b.surname
    left join ODS_WB.dbo.scur as c on b.rowno=c.rowno_custscur_cust and a.secval=c.secvalue and c.status='active'
    where year(a.appdate)=2020
    and   month(a.appdate)=6
    and   a.apptype='Mortgage'
    and   a.sourcecode in ('FI',' ')
) as x
--group by c.hrmort -- c doesn't exist outside of the above subquery
group by x.MrtCategory