I have this simple select that uses a case statement to populate the column RouteType. It works fine it shows me types 1, 3, and 33 in the column. But, when , I try to filter on it by saying where RouteType = 3, I get an error message as indicated below INVALID COLUMN NAME. What, am, I doing wrong ?
Select wa.StartDateTime, ws.Scheduledreaddate, wa.WorkFilterName, ws.WorkSetID, RouteType =
Case
when
wa.StartDateTime IS NULL or ws.ScheduledReadDate IS NULL
then 33
when
cast(wa.startdatetime as date) <= cast(ws.scheduledreaddate as date) and
wa.workfiltername IN ('DNRs','Type 2s/3s') or
((ws.worksetid %100 >= 50 and
((ws.worksetid %100 <= 69 and
substring(ws.worksetid, len(ws.worksetid) - 3, 1) = 0))))
then 1
when
cast(wa.startdatetime as date) <= cast(ws.scheduledreaddate as date) and
wa.workfiltername IN ('DNRs','Type 2s/3s') or
((ws.worksetid %100 >= 50 and
((ws.worksetid %100 <= 69 and
substring(ws.worksetid, len(ws.worksetid) - 3, 1) = 0))))
then 2
else
3
End
From fcs.dbo.WorkAssignment as wa
left outer join fcs.dbo.workset as ws
on wa.workSetID = ws.WorkSetID
-- where RouteType = 1 -- WHY IS IT THAT THIS DOES NOT WORK, INVALID COLUMN NAME????
order by 5