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.