Subscribe to this blog
Briefcase
Print
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
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.