SQL Case Statement – One to Watch for!

May 20, 2011

I’ve just finished debugging a reporting application that was outputting unexpected values. It turned out the problem was due to a case statement in a SQL query.

The query was as follows:

SELECT case
WHEN MyField BETWEEN 0 AND 100 then 'A'
WHEN MyField  BETWEEN 101 AND 200 then 'B'
WHEN MyField  BETWEEN 201 AND 300 then 'C'
END AS RANGE

This returned more rows than we expected, when I added ‘MyField’ to the query output, it became apparent that emptry string values were being included in the final case rule – So we had a number of additional ‘C’ values. The solution is simple – We needed to exclude any rows where ‘MyField’ was empty:

SELECT case
WHEN MyField BETWEEN 0 AND 100 then 'A'
WHEN MyField  BETWEEN 101 AND 200 then 'B'
WHEN MyField  BETWEEN 201 AND 300 then 'C'
END AS RANGE
WHERE MyField <> ''

You will notice that I do not have an exclusion criteria for MyField being NULL – I added this initially, but it seems these rows were excluded from the output – Because NULL is not in any of the case rules – Although I would also have expected that to be case for the empty string values!

Certainly one to be aware of – If we had not spotted this one statistics on one of the reports I look after would have been wildly out, which of course could have had serious implications when decisions are made based on the report output.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: