![]() The table contains a numeric, so the first argument is the same as CONVERT(NUMERIC(18,2), NULL), and then it tries to convert the empty string to numeric. ![]() In the second query, you are comparing to NULL first, therefore to determine the data type of the expression, it must go check the table. The NULL becomes a string, because it was referenced later, and so the underlying column (as you should see in the execution plan) was implicitly converted to a string. In the first query, you are comparing to a string first, then to NULL. The error is due to implicit conversion and the order of the expressions. When column1 is null, SQL Server can't tell you if it is equal to 'foo' or not equal to 'foo'. Since NULL means unknown, an equality or inequality comparison will yield unknown (and in this case false or, more pedantically accurate, not true). Your expression evaluates to: SELECT SUM(CASE WHEN COLUMN1 '' THEN 1 ELSE 0 END) Why would you expect 100 rows? You have 20 rows where the column is NULL. Q1 SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END) Thank you for reading this and for your assistance. Q3 What's an all-encompassing expression for my requirement of checking a column for NULLs, emptystrings and pure whitespace regardless of a column's datatype? If my columnname comes from a (cursorized) variable what should I enclose it in and compare it to? I tried working with cast to nvarchar and using LTRIM/ RTRIM, but frankly I am a bit lost at this point. Error converting data type varchar to numeric.) SELECT SUM(CASE WHEN COLUMN2 NOT IN (NULL, '') THEN 1 ELSE 0 END)įROM TABLE1 -query FAILS! (Msg 8114, Level 16, State 5, Line 1. SELECT SUM(CASE WHEN COLUMN2 NOT IN ('', NULL) THEN 1 ELSE 0 END)įROM TABLE1 - returns full rowcount (100), as expected. But the second of the queries below fails due to a reason I don't understand. Q2 I have another column COLUMN2 numeric(18, 0) filled with values with no NULLs or empty strings (but it could contain either/both). SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)įROM TABLE1 - returns 80, but I expected 100. SELECT SUM(CASE WHEN COLUMN1 NOT IN (NULL, '') THEN 1 ELSE 0 END) ![]() Why does the last of the following queries return unexpected results? SELECT SUM(CASE WHEN COLUMN1 IS NOT NULL THEN 1 ELSE 0 END) Q1 In the table TABLE1, I have a column COLUMN1 nvarchar(32) with the following data distribution: Value RowCount The table contains a mix of numeric, bit and nvarchar columns. In a table, I basically have to treat NULLs, empty strings and (pure) whitespace as 'blanks' and count the number of non-blank cells. I have searched before posting these but I didn't find any QA threads that completely answer my questions. I understand that questions similar to these pop up often around here.
0 Comments
Leave a Reply. |