Problem using a CTE in a table valued function
I am trying to avoid using a scalar valued functions in my project, so I decided to try and convert one of them into a table valued function using a CTE.
I understand that the performance of scalar valued functions is poor because it they have to be executed for each row, and SQL server cannot optimise it in any way (i.e. it acts as a black box).
Here is my first attempt at converting it into a table valued function...
CREATE FUNCTION [dbo].[fn_get_job_average] (@jobnumber VARCHAR(50))
RETURNS TABLE AS RETURN
(
WITH JobAverage AS
(
SELECT job.Jobnumber, CAST(AVG(CAST(jobMark.Mark AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS Average
FROM job
INNER JOIN jobMark
ON job.Guid = jobMark.Guid
WHERE job.Jobnumber = @jobnumber
GROUP BY job.Jobnumber
)
SELECT Jobnumber,
CASE
WHEN EXISTS(SELECT * FROM JobAverage) THEN Average
ELSE 0.0 -- This never executes???, i.e. for job records that don't have a mark nothing is returned
END AS Average
FROM JobAverage
)
I 开发者_运维知识库want to output a table with the job number and average score.
For jobs that do have the mark, it appears to be OK. That is, the average is returned along with the jobnumer.
For jobs that do not have a mark, it seems to go wrong. The ELSE part of the statement does not execute. That is, I don't get 0.0 returned as a the job average. No records are returned. Am I missing something?
Sorry I am not an experienced SQL developer, so I might have a few glaring mistakes in the above code. However, I am confused why a it doesn't work.
Thanks in advance.
Not tested but something like this should do what you want.
SELECT job.Jobnumber, COALESCE(CAST(AVG(CAST(jobMark.Mark AS DECIMAL(18,1))) AS DECIMAL(18,1)), 0.0) AS Average
FROM job
LEFT OUTER JOIN jobMark
ON job.Guid = jobMark.Guid
WHERE job.Jobnumber = @jobnumber
GROUP BY job.Jobnumber
No need to use a CTE.
BTW: What you do is that you check for Exists in the CTE Jobnumber
in the case
statement. If there are no rows in the CTE you will end up in the else part but since you use the CTE Jobnumber
in the from
clause of the main query you will not get any rows because the CTE Jobnumber
did not return any rows.
So to be perfectly clear of what is happening. The case
statement will never be executed if there are no rows in the CTE Jobnumber
.
EXISTS(SELECT * FROM JobAverage)
means "are there any rows in at all in the whole of JobAverage".
Yes, there are of course because the CASE is executing in the output rows of JobAverage
What you want is this I think:
Average of marks per job.
Zero where no marks for a job
SELECT
job.Jobnumber,
ISNULL(
CAST(AVG(CAST(jobMark.Mark AS DECIMAL(18,1))) AS DECIMAL(18,1))
,0) AS Average
FROM
job
LEFT JOIN
jobMark ON job.Guid = jobMark.Guid
WHERE job.Jobnumber = @jobnumber
GROUP BY job.Jobnumber
精彩评论