Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

We Are TOTALLY Amused: A T-SQL Error

This has to be the best error I’ve ever seen:

Msg 195, Level 15, State 10, Line 2
‘SUM’ is not a recognized built-in function name.

Uhm, I’m sorry to contradict you SQL Server, but yes it is. Here’s the code that produced that error:

SELECT keyID
, SUM(CASE WHEN printDate IS NULL THEN 1 ELSE 0 END AS printed)
, COUNT(*) total
FROM MyTable
GROUP BY keyID

The problem with this particular piece of code is, of course, in that SUM line…but it’s not the SUM that’s the problem.  SQL didn’t know what to make of the misplaced column alias “AS printed”, which should go outside the parentheses:

SELECT keyID
, SUM(CASE WHEN printDate IS NULL THEN 1 ELSE 0 END) AS printed
, COUNT(*) total
FROM MyTable
GROUP BY keyID

I don’t have a point, especially, except to say that errors can be somewhat misleading!

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

Comments

Posted by PaulHunter on 15 August 2011

I've made many fat-finger mistakes like this.  The rule of thumb I follow with SQL error messages is that they are frequently irrelevant and only point to the  nearest line where the compiler got a WTH.

Posted by EFRAINVILLA on 15 August 2011

This is obvlously a bug from the syntax checker that faced an unexpected combination of code.   I recognize however they made an effort to not just giving the classic "Incorrect syntax near %" (boring).  

As programmers we shouldn´t expect perfect compilers that tell you your  exact mistake.   Your finding is interesting anyway, so, thank you.   Regards.

Leave a Comment

Please register or log in to leave a comment.