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: 301

Filed under:

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

Related posts about sql