How to refer to a previously computed value in SQL Query statement
Posted
by Mort
on Stack Overflow
See other posts from Stack Overflow
or by Mort
Published on 2010-03-23T03:31:17Z
Indexed on
2010/03/23
3:41 UTC
Read the original article
Hit count: 297
sql
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
© Stack Overflow or respective owner