Problem using a CTE in a table valued function

Question

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.

Mikael Eriksson · Accepted Answer · 2011-06-22 12:23:53Z

5

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.

edited Jun 22, 2011 at 12:23

answered Jun 22, 2011 at 12:13

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bobbo Over a year ago

Thanks. Many thanks. I feel rather silly. I appreciate all the comments.

bobbo Over a year ago

I guess this could work too below the CTE. SELECT Jobnumber, CASE WHEN EXISTS(SELECT * FROM JobAverage) THEN (SELECT Average FROM JobAverage) ELSE 0.0 END AS Average

gbn · Accepted Answer · 2011-06-22 12:13:03Z

1

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

answered Jun 22, 2011 at 12:13

gbn

434k84 gold badges602 silver badges690 bronze badges

4 Comments

Tao Over a year ago

the first part of this answer is wrong - jobaverage is the CTE, and is already filtered, so the answer is NOT "Yes, there are of course".

gbn Over a year ago

@Tao: the last FROM is from the CTE JobAverage. For the CASE to run there must be rows in JobAverage. So EXISTS (...) will always be true. It can never hit the ELSE when there are no rows in JobAverage

Tao Over a year ago

Right, but the the OP's problem is not that it "always true when there are records" - his problem is that it's not returning anything when there are no records; I guess I just misunderstood the start of your answer as pertaining directly to the question "No records are returned. Am I missing something?" I'll go away now, sorry to bother.

gbn Over a year ago

@Tao: Now I see what you mean sorry. Just before that it says "The ELSE part of the statement does not execute".. we've all focused on this

Collectives™ on Stack Overflow

Problem using a CTE in a table valued function

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related