Why SQL case isn't working

Question

I get an error;Invalid column name 'phase'. I've tried every permutation I can think of.

<cfargument name="locationFilter" default="" />
    <cfargument name="educationFilter" default="" />
    <cfargument name="workFilter" default="" />
    <cfargument name="diversityFilter" default="" />
    <cfargument name="phaseFilter" default="" />
    <cfquery name="QMentors" datasource="#request.dsn_live#">
        SELECT  
        (case 
        when datepart(year,getdate())-cast(yearstarted as integer) > 29 then '5'
        when datepart(year,getdate())-cast(yearstarted as integer) > 13 then '4'
        when datepart(year,getdate())-cast(yearstarted as integer) > 5 then '3'
        when datepart(year,getdate())-cast(yearstarted as integer) > 1 then '2'
        else '1'
        end) as phase, *
    FROM mentors 
    WHERE 0=0
    AND mentortype='mentor' 
    AND approved='true' 
    AND active='true' 
    AND mentorcat LIKE '%general%' 
        <cfif arguments.locationFilter neq ""> AND location LIKE <cfqueryparam value="%#arguments.locationFilter#%" /></cfif>
        <cfif arguments.educationFilter neq ""> AND educationhistory LIKE <cfqueryparam value="%#arguments.educationFilter#%" /></cfif>
        <cfif arguments.workFilter neq ""> AND workhistory LIKE <cfqueryparam value="%#arguments.workFilter#%" /></cfif>
        <cfif arguments.diversityFilter eq "Diversity"> AND mentorcat LIKE <cfqueryparam value="%#arguments.diversityFilter#%" /></cfif>
        <cfif arguments.phaseFilter neq ""> AND phase = <cfqueryparam value="#arguments.phaseFilter#" /></cfif>
    ORDER BY lastname
</cfquery>

I thought I had better show the entire query.

Answer 1

try this

SELECT  
    (case 
    when datepart(year,getdate())-cast(yearstarted as integer) > 29 then '5'
    when datepart(year,getdate())-cast(yearstarted as integer) > 13 then '4'
    when datepart(year,getdate())-cast(yearstarted as integer) > 5 then '3'
    when datepart(year,getdate())-cast(yearstarted as integer) > 1 then '2'
    else '1'
    end) as phase, *
FROM table

EDIT:

You need repeat whole case in where

AND case 
    when datepart(year,getdate())-cast(yearstarted as integer) > 29 then '5'
    when datepart(year,getdate())-cast(yearstarted as integer) > 13 then '4'
    when datepart(year,getdate())-cast(yearstarted as integer) > 5 then '3'
    when datepart(year,getdate())-cast(yearstarted as integer) > 1 then '2'
    else '1'
    end) = <cfqueryparam value="#arguments.phaseFilter#" /></cfif>

Or create Select(your query) where phase = condition

Alias in where