Invalid column name 'Age'-CodePudding

enter image description here

Hi, I have created a column Age where its calculating the date difference and converting the value into integer. Next, I have created another column with Age Trend where I wrote a clause

case when Age < 120 then '<120'
        else '>120' end 'Age Trend'

for this its throwing the Invalid column name 'Age' error.

could anyone explain me how to fix it.

Thanks, Rama

CodePudding user response：

You can not use aliased column in the same level Select query. Either you need to use the same calculation again to make the 'Age Trend' column or you can use Sub Query like below :

select *, case when Age < 120 then '<120' else '>120' end 'Age Trend' 
from 
    (select [port], cast(DATEDIFF(dd, [First Detected], [Last Detected]) as int) Age
     from yourTable) AS Table1

CodePudding user response：

Please replace real query for 'Age'.

'Age' = > 'cast(DATEDIFF(dd,[First Detected],[Last Detected]) as int)'

cast(DATEDIFF(dd,[First Detected],[Last Detected]) as int) 'Age',
case when 
cast(DATEDIFF(dd,[First Detected],[Last Detected]) as int) < 120 then '<120'
else '>120' end 'Age Trend',