How to refer to a previously computed value in SQL Query statement
- by Mort
I am trying to add a CASE statement to the end of my SQL query to compute a value depending upon another table value and a previously computed value in the SELECT. The error is returned that DelivCount is an invalid column name. Is there a better way to do this or am I missign something?
SELECT jd.FullJobNumber, jd.ProjectTitle, jd.ClientName, jd.JobManager, jd.ProjectDirector, jd.ServiceGroup, jd.Status, jd.HasDeliverables, jd.SchedOutsideJFlo, jd.ReqCompleteDate,(SELECT COUNT(*)FROM DeliverablesSchedule ds WHERE jd.FullJobNumber = ds.FullJobNumber) as DelivCount, SchedType =
CASE
WHEN (jd.SchedOutsideJFlo = 'Yes')
THEN 'outside'
WHEN (jd.HasDeliverables = 'No ')
THEN 'none'
WHEN (DelivCount > 0)
THEN 'has'
WHEN (jd.HasDeliverables = 'Yes' AND DelivCount = 0)
THEN 'missing'
ELSE 'unknown'
END
FROM JobDetail jd