How to refer to a previously computed value in SQL Query statement
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开发者_StackOverflow社区 = 0)
THEN 'missing'
ELSE 'unknown'
END
FROM JobDetail jd
try this
SELECT
Z.*,
SchedType =
CASE
WHEN (Z.SchedOutsideJFlo = 'Yes')
THEN 'outside'
WHEN (Z.HasDeliverables = 'No ')
THEN 'none'
WHEN (Z.DelivCount > 0)
THEN 'has'
WHEN (Z.HasDeliverables = 'Yes' AND Z.DelivCount = 0)
THEN 'missing'
ELSE 'unknown'
END
FROM
(
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
FROM JobDetail jd
)
Z
try this, which should run a lot faster:
SELECT
jd.FullJobNumber, jd.ProjectTitle, jd.ClientName, jd.JobManager, jd.ProjectDirector, jd.ServiceGroup, jd.Status, jd.HasDeliverables, jd.SchedOutsideJFlo, jd.ReqCompleteDate
,ds.DelivCount
,SchedType =CASE
WHEN (jd.SchedOutsideJFlo = 'Yes')
THEN 'outside'
WHEN (jd.HasDeliverables = 'No ')
THEN 'none'
WHEN (ds.DelivCount > 0)
THEN 'has'
WHEN (jd.HasDeliverables = 'Yes' AND ds.DelivCount = 0)
THEN 'missing'
ELSE 'unknown'
END
FROM JobDetail jd
LEFT OUTER JOIN (SELECT
FullJobNumber, COUNT(*) AS DelivCount
FROM DeliverablesSchedule
GROUP BY FullJobNumber
) ds ON jd.FullJobNumber = ds.FullJobNumber
The original query uses a subquery:
A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed.
by the very nature of a sub query, it must be run repeatedly, once for each row. I have rewritten the query to use a derived table, which is evaluated one time to find all of the counts and is then joined to the proper rows. This allows for the DelivCount value to be referred to as any column would be when joined from another table, and should speed up this query.
精彩评论