Sql case when error - Исправление ошибок и поиск оптимальных решений проблем

Я уверен, что вы уже знакомы с основами SQL CASE, поэтому я не буду мучит вас длинным введением. Давайте углубимся в понимание того, что происходит под капотом.

1. SQL CASE не всегда оценивается последовательно

Выражения в SQL CASE по большей части оцениваются последовательно или слева направо. Хотя совсем другое дело, когда они используются с агрегатными функциями. Давайте рассмотрим пример:

-- сначала оценивается агрегатная функция и генерирует ошибку

DECLARE @value INT = 0;

SELECT CASE WHEN @value = 0 THEN 1 ELSE MAX(1/@value) END;

Вышеприведенный код выглядит обычно. Если я спрошу вас, какой результат будет получен, вы, вероятно, ответите 1. Визуальная проверка скажет нам это, поскольку переменная @value установлена в 0. Если @value равна 0, то результат равен 1.

Но не в этом случае. Вот действительный результат, полученный в SQL Server Management Studio:

Msg 8134, Level 16, State 1, Line 4

Divide by zero error encountered.

Но почему?

Когда условное выражение использует агрегатные функции типа MAX() в SQL CASE, они оцениваются в первую очередь. Таким образом, MAX(1/@value) вызывает ошибку деления на нуль, поскольку @value равна 0.

Ситуация становится еще более неприятной, когда скрыта. Я объясню это позже.

2. Простое выражение SQL CASE оценивается многократно

Ну и что?

Хороший вопрос. Действительно, здесь вообще нет никаких проблем, если вы используете литералы или простые выражения. Но если вы используете подзапросы в качестве условного выражения, вы сильно удивитесь.

Прежде, чем проверять пример ниже, лучше восстановить отсюда копию базы данных. Мы будем использовать её в последующих примерах.

Теперь рассмотрим следующий очень простой пример:

SELECT TOP 1 manufacturerID FROM SportsCars

Очень простой, правда? Он возвращает 1 строку с одним столбцом данных. STATISTICS IO показывает минимальное число логических чтений.

Рис.1. Логические чтения таблицы SportsCars до использования запроса в качестве подзапроса в SQL CASE

Замечание для непосвященных. Чем больше логических чтений, тем медленнее запрос. О логических чтениях можно почитать здесь.

План выполнения тоже показывает простой процесс:

Рис.2. План выполнения для запроса к SportsCar до его использования как подзапроса в SQL CASE

Давайте теперь поместим этот запрос в выражение CASE:

-- Использование подзапроса в SQL CASE

DECLARE @manufacturer NVARCHAR(50)

SET @manufacturer = (CASE (SELECT TOP 1 manufacturerID FROM SportsCars)

				WHEN 6 THEN 'Alfa Romeo'

				WHEN 21 THEN 'Aston Martin'

				WHEN 64 THEN 'Ferrari'

				WHEN 108 THEN 'McLaren'

				ELSE 'Others'

		     END)

SELECT @manufacturer;

Анализ

Скрестите пальцы, поскольку сейчас логические чтения увеличатся в 4 раза.

Рис.3. Логические чтения после использования подзапроса в SQL CASE

Удивительно! По сравнению всего с двумя логическими чтениями на рис.1 мы получили в 4 раза больше. Таким образом, запрос стал в 4 раза медленнее. Как это могло произойти? Мы видим подзапрос только в одном месте.

Но это не конец истории. Посмотрите план выполнения:

Рис.4. План выполнения после использования простого запроса в качестве выражения подзапроса в SQL CASE

Мы видим 4 экземпляра операторов Top и Index Scan на рис.4. Если каждый Top и Index Scan потребляет 2 логических чтения, это объясняет, почему число логических чтений стало 8 на рис.3. И, поскольку каждый Top и Index Scan имеют 25% стоимости, это подтверждает сказанное.

Но это еще не все. Свойства оператора Compute Scalar показывают, как обрабатывается весь оператор.

Рис.5. Свойства Compute Scalar показывают 4 выражения CASE WHEN

Мы видим 3 выражения CASE WHEN в свойстве Defined Values оператора Compute Scalar. Это выглядит так, как будто простое выражение CASE стало поисковым выражением CASE типа:

DECLARE @manufacturer NVARCHAR(50)

SET @manufacturer = (CASE 

		     WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 6 THEN 'Alfa Romeo'

		     WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 21 THEN 'Aston Martin'

		     WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 64 THEN 'Ferrari'

		     WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 108 THEN 'McLaren'

		     ELSE 'Others'

		     END)

SELECT @manufacturer;

Хорошее исправление? Давайте посмотрим логические чтения в STATISTICS IO:

Рис.6. Логические чтения после извлечения подзапроса из выражения CASE

Мы видим меньше логических чтений в модифицированном запросе. Извлечение подзапроса и присвоение результата переменной получается значительно лучше. Что насчет плана выполнения? Посмотрите ниже:

Рис.7. План выполнения после извлечения подзапроса из выражения CASE

Оператор Top и Index Scan появляются однажды, а не 4 раза. Замечательно!

На заметку: Не используйте подзапрос в качестве условия в операторе CASE. Если необходимо получить значение, поместите сначала результат подзапроса в переменную. Затем используйте эту переменную в выражении CASE.

Эти три встроенные функции тайно преобразуются в SQL CASE

Есть секрет, и SQL CASE имеет к нему отношение. Если вы не знаете, как ведут себя эти 3 функции, вы не будете знать, что совершаете ошибку, которую мы пытались избежать в пунктах №1 и №2 выше. Вот они:

COALESCE

CHOOSE

Давайте рассмотрим их по очереди.

IIF

Я использовал Immediate IF, или IIF, в Visual Basic и Visual Basic for Applications. Это является также эквивалентом тернарного оператор в C#: <условие> ? <результат, если истинно> : <результат, если ложно>.

Эта функция принимает условие и возвращает 1 из 2 аргументов в зависимости от результатов условия. И эта функция также имеется в T-SQL.

Но это просто обертка более длинного выражения CASE. Откуда нам это известно? Давайте проверим пример.

SELECT IIF((SELECT Model FROM SportsCars WHERE SportsCarID = 1276) = 

'McLaren Senna', 'Yes', 'No');

Результатом этого запроса является ‘No’. Однако проверьте план выполнения, а также свойства Compute Scalar.

Рис.8. IIF оказывается CASE WHEN в плане выполнения

Поскольку IIF является CASE WHEN, как вы думаете, что произойдет, если выполнить что-то подобное этому?

DECLARE @averageCost MONEY = 1000000.00;

DECLARE @noOfPayments TINYINT = 0;   -- умышленно вызвать ошибку

SELECT IIF((SELECT Model FROM SportsCars WHERE SportsCarID = 1276) = 'SF90 Spider', 83333.33,MIN(@averageCost / @noOfPayments));

Будет получена ошибка деления на нуль, если @noOfPayments равен нулю. То же самое происходило в первом случае, рассмотренном ранее.

Вы можете спросить, что вызывает эту ошибку, поскольку результатом запроса является TRUE, и должно получиться 83333.33. Опять вернитесь к случаю 1.

Таким образом, если вы столкнулись с такой ошибкой при использовании IIF, виноват SQL CASE.

COALESCE

COALESCE — это также сокращенная форма выражения SQL CASE. Она оценивает список значений и возвращает первое не-NULL значение. Вот пример, который показывает, что подзапрос вычисляется дважды.

SELECT 

COALESCE(m.Manufacturer + ' ','') + sc.Model AS Car 

FROM SportsCars sc

LEFT JOIN Manufacturers m ON sc.ManufacturerID = m.ManufacturerID

Давайте посмотрим план выполнения и свойство Defined Values оператора Compute Scalar.

Рис.9. COALESCE преобразуется в SQL CASE в плане выполнения

Разумеется SQL CASE. Нигде не упоминается COALESCE в окне Defined Values. Это доказывает тайный секрет этой функции.

Но это не все. Сколько раз вы увидели [Vehicles].[dbo].[Styles].[Style] в окне Defined Values? ДВАЖДЫ! Это согласуется с официальной документацией Microsoft. Представьте, что один из аргументов в COALESCE является подзапросом. Тогда получаем удвоение логических чтений и замедление выполнения.

CHOOSE

Наконец, CHOOSE. Она подобна функции CHOOSE в MS Access. Она возвращает одно значение из списка значений на основе позиции индекса. Она также действует как индекс массива.

Давайте посмотрим, сможем ли мы получить трансформацию в SQL CASE в примере. Проверьте нижеприведенный код:

;WITH McLarenCars AS 

(

SELECT 

 CASE 

	WHEN sc.Model IN ('Artura','Speedtail','P1/ P1 GTR','P1 LM') THEN '1'

	ELSE '2'

 END AS [type]

,sc.Model

,s.Style

FROM SportsCars sc

INNER JOIN Styles s ON sc.StyleID = s.StyleID

WHERE sc.ManufacturerID = 108

)

SELECT 

 Model

,Style

,CHOOSE([Type],'Hybrid','Gasoline') AS [type]

FROM McLarenCars

Это наш пример с CHOOSE. Теперь давайте посмотрим план выполнения и свойство Defined Values в операторе Compute Scalar:

Рис.10. Как видно в плане выполнения CHOOSE преобразуется в SQL CASE

Вы видите ключевое слово CHOOSE в окне Defined Values на рис.10? Как насчет CASE WHEN?

Подобно предыдущим примерам, эта функция CHOOSE есть просто оболочка для более длинного выражения CASE. И поскольку запрос имеет 2 пункта для CHOOSE, ключевые слова CASE WHEN появляются дважды. Смотрите в окне Defined Values красные прямоугольники.

Однако CASE WHEN появлялось более двух раз. Это происходит из-за выражения CASE во внутреннем запросе CTE. Если посмотреть внимательно, эта часть внутреннего запроса также появляется дважды.

На заметку

Теперь, когда все секреты раскрыты, что мы узнали?

SQL CASE ведет себя иначе, когда используются агрегатные функции. Будьте внимательны при передаче аргументов в агрегатные функции типа MIN, MAX или COUNT.

Простое выражение CASE будет оцениваться несколько раз. Имейте это в виду и избегайте передачи подзапроса. Хотя это синтаксически корректно, это повредит производительности.

IIF, CHOOSE и COALESCE имеют грязные секреты. Помните это, передавая значения в эти функции. Это вызовет преобразование к SQL CASE. В зависимости от значений вы можете получить ошибку или ухудшить производительность.

Надеюсь, что различие в поведении SQL CASE было полезным для вас.

Источник

Remove From My Forums

Question
Hi All,

I am having problems with the following SELECT CASE statement when trying to handle NULL values. In the following select

SELECT

st3.description

, CASE st3.description WHEN NULL THEN ‘I am Null’ ELSE ‘This is else’ END AS Expr2

, ISNULL(st3.description, ‘Null Value’) AS Expr3

FROM structure AS st3

When st3.description is NULL I want EXPR2 to display ‘I AM NULL’ but ‘WHEN NULL’ does not seem to work. Column ‘description’ is of type VARCHAR(50).

This is the results I get are as follows:-

description Expr2 Expr3

NULL This is else Null Value

NULL This is else Null Value

Amber This is else Amber

Amber This is else Amber

How do I pick up NULL values in a ‘case when’ statement.

TIA

Andi

Answers

Here is the proper syntax for cheking a NULL value in a CASE statment.

Declare @test int

Select Case When @test IS NULL THEN ‘Value is Null’ ELSE ‘Value is not Null’ END Test
Hi Vladimir,

The CASE statement has 2 forms which need to be understood.

Form 1

CASE scalar_expression1

WHEN scalar_expression2 THEN …

WHEN scalar_expression3 THEN …

ELSE …

END

In this form, scalar_expression1 is comapred sequntially using = with each WHEN clause and the first match is executed. However NULL = NULL if false and hence you can’t use this form in your SQL

Form 2

CASE

WHEN boolean_expression1 THEN …

WHEN boolean_expression2 THEN …

ELSE …

END

In this form each boolean_expression is evaluated sequentially and the first to return true is executed. Here you can use the IS operator that is used to test for NULL values. e.g. WHEN column IS NULL THEN.

So in your case you want to use:

SELECT st3.description

, CASE WHEN st3.description IS NULL THEN ‘I am Null’ ELSE ‘I am NOT Null’ END Expr2

, ISNULL(st3.description, ‘Null Value’) AS Expr3

FROM structure AS st3

Источник

The main purpose of a SQL CASE expression returns a value based on one or more conditional tests. Use CASE expressions anywhere in a SQL statement expression is allowed. Though truly an expression, some people refer to them as “CASE statements.” This most likely stems from their use in programming languages.

The SQL CASE expression is extremely versatile and used throughout SQLServer queries. In particular it is used in the SELECT column list, GROUP BY, HAVING, and ORDER BY clauses. The CASE expression also standardizes (beautify) data or performs checks to protect against errors, such as divide by zero.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. Getting Started Using SQL Server using my free guide and free Microsoft tools.

SQL Server CASE Statement Forms
- CASE expression Simple Form
- CASE expression Searched Form
- Comparison of Simple and Searched Forms SQL CASE
Use CASE to Compare a Range of Values
Data Transformation Example
Data Standardization Example
SQL CASE Prevents SQL Errors!
CASE in SELECT
- CASE expression in ORDER BY
- CASE expression in GROUP BY
- CASE in WHERE Clause
- CASE with CTE
Wrap Up

SQL Server CASE Statement Forms

There are two forms for the CASE clause: simple and searched. Both forms return a result based on testing an expression. Though technically expressions, you’ll see many people refer to it as a statement.

The simple SQL CASE statement is used for equality tests. It tests one expression against multiple values, this makes it great for transforming one set of values, such as abbreviations to their corresponding long form.

The searched SQL CASE statement uses a more comprehensive expression evaluation format. It is good when you wish to work with ranges of data, such as salary ranges or ages.

We first start out with the simple form, and then cover searched.

CASE expression Simple Form

The simple form of the CASE expression compares the results of an expression with a series of tests and return a “result” when the “test” returns true.

The general form for a simple form CASE expression is:

CASE expression
    WHEN test THEN result
    …
    ELSE otherResult
 END

The ELSE statement is optional in a CASE expression. It returns “otherResult” when no matches are made and ELSE is present. If there is no ELSE in the CASE statement, then it returns NULL.

The ELSE clause is a great way to catch bad or unexpected data values, and return a result other than NULL.

Here are some things to consider when using the simple CASE expression:

Allows only equality comparisons.
Evaluates tests are evaluated in the order defined.
Returns the result corresponding to the first TRUE test.
If no match is made, case returns NULL unless ELSE is present.

CASE expression Searched Form

The search form of the CASE expression allows for more versatile testing. Use it to evaluate a greater range of tests. In fact, any Boolean expression qualifies as a test.

A searched from CASE expression has this format

CASE
    WHEN booleanExpression THEN result
    …
    ELSE otherResult
 END

With the searched form, use WHEN clauses to evaluate Boolean expressions. The result of the first TRUE Boolean expression is returned.

Below is the searched CASE form of the employee gender example from the previous section.

SELECT JobTitle,
        CASE
           WHEN Gender = 'M' THEN 'Male'
           WHEN Gender = 'F' THEN 'Female'
           ELSE 'Unknown Value'
        END
 FROM   HumanResources.Employee

We also used this same example for the simple SQL case statement. I did this so you could see the subtle difference. Notice that each WHEN clause now contains the test as a Boolean expression.

Comparison of Simple and Searched Forms SQL CASE

Here are the statements side-by-side:

Simple versus Searched CASE expression

I tend to use the searched CASE expression format in all my SQL. This reason is simple, I only have to remember one format!

Since we’re testing Boolean expressions, the searched CASE statement isn’t limited to just equality tests.

Use CASE to Compare a Range of Values

This makes this form really good for comparing ranges of values. Perhaps the sales manager of Adventure Works wants to organize products by price range. How could this be done with SQL?

Given the following names and ranges provided by the sales manager, we can construct a CASE expression to compare the ListPrice to a range of values and then return the appropriate price range name.

The case statement is placed in the SELECT column list and returns a character value. Here’s the SQL that does the trick:

SELECT Name,
        ListPrice,
        CASE
           WHEN ListPrice = 0 THEN 'No Price'
           WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
           WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
           WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
           WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
           WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
           WHEN ListPrice > 2000 THEN 'Luxury'
           ELSE 'UNLISTED'
        END as PriceRange
 FROM   Production.Product

When you run this query you’ll see PriceRange listed and displaying values according to the ranges specified in the CASE expression:

CASE Statement Results

Data Transformation Example

There are several reasons to use a CASE statement. The first is to transform data from one set of values to another. For instance, to display an employee’s gender as “Male” or “Female,” when your data is encoded as “M” or “F,” use a CASE expression to test for the single character representation and return its corresponding long form.

The example for this is:

SELECT JobTitle,
        CASE Gender
           WHEN 'M' THEN 'Male'
           WHEN 'F' THEN 'Female'
           ELSE 'Unknown Value'
        END
 FROM   HumanResources.Employee

Data Standardization Example

Similarly you can use a simple CASE clause to standardize several values into one. Extending our example maps several variations to either Male or Female:

SELECT JobTitle,
        CASE Gender
           WHEN 'M' THEN 'Male'
           WHEN '0' THEN 'Male'
           WHEN 'F' THEN 'Female'
           WHEN '1' THEN 'Female'
           ELSE 'Unknown Value'
        END
 FROM   HumanResources.Employee

You may be wondering if you could just create another table in your database and use that to lookup the values. I would tend to agree that would be the best, but in many situations you won’t have permission to create tables in the database. In this case you’re left to your wits a SELECT statement’s provides.

SQL CASE Prevents SQL Errors!

CASE statements can also be used to help prevent errors. A good example is to test for valid values within expressions such as when you divide numbers.

Consider

SELECT ProductID,
        Name,
        ProductNumber,
        StandardCost,
        ListPrice,
        StandardCost / ListPrice as CostOfGoodSold
 FROM   Production.Product

This statement return the message

Divide by zero error encountered.

By using a CASE clause we can ensure we don’t inadvertently divide by zero.

SELECT ProductID,
       Name,
       ProductNumber,
       StandardCost,
       ListPrice,
       CASE
          WHEN ListPrice = 0 Then NULL
          ELSE StandardCost / ListPrice
       END as CostOfGoodSold
FROM   Production.Product

A CASE expression can be used wherever an expression can be used. This means you can use it to return a column value result or even use it in an ORDER BY clause.

CASE in SELECT

In the following section we’ll explore using CASE in the ORDER BY and GROUP BY clauses.

CASE expression in ORDER BY

Continuing on with the sales manager request, suppose she also wants to see the products sorted by price range and then product name. We’ve seen how we can display the price ranges as a column, but how do we sort?

Actually it is pretty easy. Since CASE is an expression, we can use it as once of the values from which order the results. Remember, we aren’t limited to just sorting table columns, we can also sort an expression.

Here is the query to sort by the price range.

SELECT   Name,
         ListPrice
FROM     Production.Product
ORDER BY CASE
            WHEN ListPrice = 0 THEN 'No Price'
            WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
            WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
            WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
            WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
            WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
            WHEN ListPrice > 2000 THEN 'Luxury'
            ELSE 'UNLISTED'
         END,
         Name

We can then add CASE statement to SELECT list to also display the price range.

SELECT   Name,
          ListPrice,
          CASE
             WHEN ListPrice = 0 THEN 'No Price'
             WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
             WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
             WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
             WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
             WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
             WHEN ListPrice > 2000 THEN 'Luxury'
             ELSE 'UNLISTED'
          END as PriceRange
 FROM     Production.Product
ORDER BY CASE
            WHEN ListPrice = 0 THEN 'No Price'
            WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
            WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
            WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
            WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
            WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
            WHEN ListPrice > 2000 THEN 'Luxury'
            ELSE 'UNLISTED'
         END,
         Name

As you can see, things start to get complicated. Do you see how the CASE statement is repeated in both the SELECT list and ORDER BY? Fortunately, we can simplify this a bit, but removing the CASE statement from the ORDER BY and replacing it with the SELECT list CASE expression’s alias name PriceRange as so:

 SELECT   Name,
          ListPrice,
          CASE
             WHEN ListPrice = 0 THEN 'No Price'
             WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
             WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
             WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
             WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
             WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
             WHEN ListPrice > 2000 THEN 'Luxury'
             ELSE 'UNLISTED'
          END as PriceRange
 FROM     Production.Product
ORDER BY  PriceRange, Name

CASE expression in GROUP BY

Now that we’ve given the sales manager a detailed listing she wants to see summary data – doesn’t it ever end? In my experience it doesn’t, so knowing lots of SQL to satiate customer demands is your key to success.

Anyways, the good news is we can use the CASE expression we’ve built to create summary groups. In the following SQL we’re grouping the data by PriceRange. Summary statistics on the minimum, maximum, and average ListPrice are created.

SELECT
   CASE
             WHEN ListPrice = 0 THEN 'No Price'
             WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
             WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
             WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
             WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
             WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
             WHEN ListPrice > 2000 THEN 'Luxury'
             ELSE 'UNLISTED'
          END as PriceRange,
          Min(ListPrice) as MinPrice,
          Max(ListPrice) as MaxPrice,
          AVG(ListPrice) as AvgPrice,
          Count(ListPrice) as NumberOfProducts
 FROM     Production.Product
 GROUP BY  CASE
             WHEN ListPrice = 0 THEN 'No Price'
             WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
             WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
             WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
             WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
             WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
             WHEN ListPrice > 2000 THEN 'Luxury'
             ELSE 'UNLISTED'
          END
  ORDER BY MinPrice

Unlike the ORDER BY clause, we can’t reference the column alias PriceRange in the GROUP BY. The entire CASE expression has to be repeated. Here are the results of our query:

Results – CASE Expression in GROUP BY

CASE in WHERE Clause

You can also use a CASE in the WHERE clause. This is particularly handy if want one of several values to evaluate to a condition.

Continuing with the Gender example, we can write the following CASE in a WHERE clause to test for female employees:

SELECT JobTitle, Gender
FROM   HumanResources.Employee
WHERE  CASE Gender
          WHEN 'M' THEN 'Male'
          WHEN '0' THEN 'Male'
          WHEN 'F' THEN 'Female'
          WHEN '1' THEN 'Female'
          ELSE 'Unknown Value'
        END = 'Female'

Using SQL CASE in WHERE Clause

You could have also solved this problem using BOOLEAN logic, or a IN clause, but as the logic get more complex, you may find it is easier to express it with a CASE, rather than those other techniques. It can make your code easier to read and maintain.

CASE with CTE

Speaking of readability and maintainability, why not just wrap the CASE expression within a Common Table Express (CTE) to avoid “sprinkling” the CASE logic through out your code?

Here we use a CTE to define the CASE result once, then reuse that result in the outer query:

;WITH cteEmployee as
(
   SELECT JobTitle, Gender,
      CASE Gender
         WHEN 'M' THEN 'Male'
         WHEN '0' THEN 'Male'
         WHEN 'F' THEN 'Female'
         WHEN '1' THEN 'Female'
         ELSE 'Unknown Value'
      END GenderResult
FROM HumanResources.Employee
)
SELECT JobTitle, Gender, GenderResult
FROM cteEmployee
WHERE GenderResult = 'Female'
ORDER BY GenderResult

To make it more clear, check out the following which shows how this is done with a CASE statement. Notice the cteEmployee is used to calculate the GenderResult, which is then reused in the SELECT, WHERE, and ORDER BY clauses!

Using a CASE clause within CTE

To learn more about Common Table Expressions, check out Common Table Expressions – The Ultimate Guide.

Wrap Up

As you can see, using CASE expressions adds versatility to your SQL statements. They not only allow you to transform data from one set of values to another, but can also be used to ensure statements don’t return errors.

Out of the two forms, simple and searched, I tend use the search form. The reason is that the simple form is limited to equality tests; whereas, the searched form can do that and more.

Since CASE expressions are expressions and not statements or clauses, they can be used where any other expression is used. That mean you can use throughout the SELECT statement and elsewhere in SQL.

Источник

Hello All —

I feel like a dope here, but I need some help. I have a case statement which is throwing a conversion error and i’m trying to figure this out. All fields involved in the CASE are all numeric (int), I check them with the ISNUMERIC() function. However, when run the following statement I get the corresponding error. When I cast everything to VARCHAR(), the AGE >= THRESHOLD comparison doesn’t work correctly.

any ideas?
```
SELECT CASE WHEN PROCSTATUS_NUM = 1 AND AGE >= THRESHOLD THEN 0 ELSE 4 END as Sort
```
ERROR:

Msg 245, Level 16, State 1, Line 11

Conversion failed when converting the ****** value ‘******’ to data type ******.
Steve Collins

SSCommitted

Points: 1895

Maybe the obscured error message contains some clues

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Jonathan AC Roberts

SSCoach

Points: 17793

From the limited information you have provided in your question and the error message I would guess that column PROCSTATUS_NUM is not a numeric data type (maybe it’s character?) and contains a value that cannot be converted to a number (maybe alphabetic or punctuation?).
MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

Polymorphist wrote:

When I cast everything to VARCHAR(), the AGE >= THRESHOLD comparison doesn’t work correctly.

you need to give us a bit more — but if you are casting everything to varchar then you will get isssues — for example you would expect 21 to come before 112 — but since it’s a char then 112 comes first

at least give us the value types it is trying to convert from and to (in your error message). -i’m pretty sure that bigint, float, nvarchar etc are not confidential as part of a script

MVDBA
Polymorphist

SSCommitted

Points: 1909

Thanks for the replies. I have to apologize, this query is from an OLTP system behind a very large healthcare application, it’s very messy. I essentially got lost inside the code and didn’t realize that there was another field called THRESHOLD which had an IIF string formatted for use inside Excel, all I had to do was remove that.

I’m trying to move this code out of a VBScript file and into SSRS, and though I love the challenge, the code here is….ugh. I’m gonna make it better, though, i’m gonna make it better….

Thanks for the responses 🙂
Jason A. Long

SSC-Insane

Points: 23756
ISNUMERIC() doesn’t do what you think it does. I think you’d be surprised by what all SQL Server considers «numeric».
```
SELECT 
cv.char_val,
is_numeric = ISNUMERIC(cv.char_val),
is_int = TRY_CONVERT(INT, cv.char_val),
is_decimal = TRY_CONVERT(DECIMAL(9, 2), cv.char_val),
is_float = TRY_CONVERT(FLOAT, cv.char_val)
FROM
( VALUES ('12345'), ('12345.678'), ('$100.00'), ('12345.54321E54'), ('5E+4'), ('123,456,90.0'), ('123.456.78,12') ) cv (char_val);
```

Jeffrey Williams

SSC Guru

Points: 90002

Jason A. Long wrote:

ISNUMERIC() doesn’t do what you think it does. I think you’d be surprised by what all SQL Server considers «numeric».

SELECT 
cv.char_val,
is_numeric = ISNUMERIC(cv.char_val),
is_int = TRY_CONVERT(INT, cv.char_val),
is_decimal = TRY_CONVERT(DECIMAL(9, 2), cv.char_val),
is_float = TRY_CONVERT(FLOAT, cv.char_val)
FROM
( VALUES ('12345'), ('12345.678'), ('$100.00'), ('12345.54321E54'), ('5E+4'), ('123,456,90.0'), ('123.456.78,12') ) cv (char_val);

You should add the money data type to this — it shows that ISNUMERIC recognizes money formats and will convert those correctly.

Jeff Moden

SSC Guru

Points: 1002259

It’ll also say that ‘,,,,,,,,,,,’ is a valid numeric. And the cool part about converting to MONEY during imports (if you don’t need more than 4 decimal places) is that it will drop most of the «white space control characters» such as TAB, Cr, Lf, FF, Vt, and even «hard spaces» from after the number being converted.
Jason A. Long

SSC-Insane

Points: 23756

Jeffrey Williams wrote:

You should add the money data type to this — it shows that ISNUMERIC recognizes money formats and will convert those correctly.

I did. The 3rd value has a $…
Jeff Moden

SSC Guru

Points: 1002259

Jason A. Long wrote:

Jeffrey Williams wrote:

You should add the money data type to this — it shows that ISNUMERIC recognizes money formats and will convert those correctly.

I did. The 3rd value has a $…

… and commas! 😀
MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

be careful with the money data type — Vietnamese Dong tend to overflow after a certain value. (i’m talking building a hospital costs, not buying a chocolate bar)

MVDBA
m-scally

SSC Journeyman

Points: 99
You could try using TRY_CAST or TRY_CONVERT in your CASE but this will just hide the problem.

You’re better to deal with the issue once then ignore it and deal with it constantly.
```
SELECT 
CASE 
WHEN 
ISNULL(TRY_CONVERT(INT, PROCSTATUS_NUM),0) = 1 
AND ISNULL(TRY_CONVERT(INT, AGE),0) >= ISNULL(TRY_CONVERT(INT, THRESHOLD),0)
THEN 0 
ELSE 4 
END AS Sort
```

Viewing 12 posts — 1 through 11 (of 11 total)

Источник

The SQL CASE statement allows you to perform IF-THEN-ELSE functionality within an SQL statement. Learn more about this powerful statement in this article.

This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.

What Does the SQL CASE Statement Do?

The CASE statement allows you to perform an IF-THEN-ELSE check within an SQL statement.

It’s good for displaying a value in the SELECT query based on logic that you have defined. As the data for columns can vary from row to row, using a CASE SQL expression can help make your data more readable and useful to the user or to the application.

It’s quite common if you’re writing complicated queries or doing any kind of ETL work.

The syntax of the SQL CASE expression is:

CASE [expression]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2 ...
WHEN condition_n THEN result_n
ELSE result
END case_name

The CASE statement can be written in a few ways, so let’s take a look at these parameters.

Parameters of the CASE Statement

The parameters or components of the CASE SQL statement are:

expression (optional): This is the expression that the CASE statement looks for. If we’re comparing this to an IF statement, this is the check done inside the IF statement (e.g. for IF x > 10, the expression would be “x > 10”
condtion_1/condition_n (mandatory): These values are a result of the expression parameter mentioned. They are the possible values that expression can evaluate to. Alternatively, they can be an expression on their own, as there are two ways to write an SQL CASE statement (as explained below). They also relate to the IF statement in the IF-THEN-ELSE structure.
result_1/result_n (mandatory): These values are the value to display if the related condition is matched. They come after the THEN keyword and relate to the THEN part of the IF-THEN-ELSE structure.
result (optional): This is the value to display if none of the conditions in the CASE statement are true. It is the ELSE part of the IF-THEN-ELSE structure and is not required for the CASE SQL statement to work.
case_name (optional): This value indicates what the column should be referred to as when displayed on the screen or from within a subquery. It’s also called the column alias.

Simple and Searched CASE Expressions

There are actually two ways to use an SQL CASE statement, which are referred to as a “simple case expression” or a “searched case expression”.

Simple CASE expression

The expression is stated at the beginning, and the possible results are checked in the condition parameters.

For example:

CASE name
  WHEN 'John' THEN 'Name is John'
  WHEN 'Steve' THEN 'Name is Steve'
END

Searched CASE expression

The expressions are used within each condition without mentioning it at the start of the CASE statement.

For example:

CASE
  WHEN name = 'John' THEN 'Name is John'
  WHEN name = 'Steve' THEN 'Name is Steve'
END

All data types for the expression and conditions for the Simple expressions, and all of the results for both expression types must be the same or have a numeric data type. If they all are numeric, then the database will determine which argument has the highest numeric precedence, implicitly convert the remaining argument to that data type, and return that datatype. Basically, it means the database will work out which data type to return for this statement if there is a variety of numeric data types (NUMBER, BINARY_FLOAT or BINARY_DOUBLE for example).

In SQL, IF statements in SELECT statements can be done with either of these methods. I’ll demonstrate this using more examples later in this article.

Want to see guides like this for all other Oracle functions? Check out this page here that lists all SQL functions along with links to their guides.

Examples of the CASE Statement

Here are some examples of the SQL CASE statement in SELECT queries. I find that examples are the best way for me to learn about code, even with the explanation above.

Simple CASE Statement

SELECT
first_name, last_name, country,
CASE country
  WHEN 'USA' THEN 'North America'
  WHEN 'Canada' THEN 'North America'
  WHEN 'UK' THEN 'Europe'
  WHEN 'France' THEN 'Europe'
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;

The results are:

FIRST_NAME	LAST_NAME	COUNTRY	CONTINENT
Adam	Cooper	USA	North America
John	Smith	USA	North America
Mark	Allan	UK	Europe
Sally	Jones	USA	North America
Steve	Brown	Canada	North America

This example is using the simple case statement structure. Notice how the expression (in this case the “country” field) comes right after the CASE keyword. This means the WHEN expressions are all compared to that field.

This example, like most of the examples here, shows the continent of each customer, based on their country.

Searched Case

SELECT first_name, last_name, country,
CASE
  WHEN country = 'USA' THEN 'North America'
  WHEN country = 'Canada' THEN 'North America'
  WHEN country = 'UK' THEN 'Europe'
  WHEN country = 'France' THEN 'Europe'
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;

FIRST_NAME	LAST_NAME	COUNTRY	CONTINENT
Adam	Cooper	USA	North America
John	Smith	USA	North America
Mark	Allan	UK	Europe
Sally	Jones	USA	North America
Steve	Brown	Canada	North America

This example performs the same check as the other examples but uses the searched case method. This means that each expression in the WHEN section is evaluated individually. It gives the same result as the previous example though.

Searched Case with Numbers

SELECT first_name, last_name, employees,
CASE
  WHEN employees < 10 THEN 'Small'
  WHEN employees >= 10 AND employees <= 50 THEN 'Medium'
  WHEN employees >= 50 THEN 'Large'
END SizeOfCompany
FROM customers
ORDER BY first_name, last_name;

FIRST_NAME	LAST_NAME	EMPLOYEES	SIZEOFCOMPANY
Adam	Cooper	55	Large
John	Smith	4	Small
Mark	Allan	23	Medium
Sally	Jones	10	Medium
Steve	Brown	15	Medium

This example performs a searched CASE using a number field, which is the number of employees. Notice how the second WHEN expression has two checks – to see if the number is between 10 and 50.

CASE Statement With IN Clause

SELECT first_name, last_name, country,
CASE
  WHEN country IN ('USA', 'Canada') THEN 'North America'
  WHEN country IN ('UK', 'France') THEN 'Europe'
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;

FIRST_NAME	LAST_NAME	COUNTRY	CONTINENT
Adam	Cooper	USA	North America
John	Smith	USA	North America
Mark	Allan	UK	Europe
Sally	Jones	USA	North America
Steve	Brown	Canada	North America

This example looks up the continent of the customer again. However, it uses an IN clause, which means the value is checked to see if it is in the IN parameter. It should have the same result, but it’s a bit cleaner and has less code.

Nested CASE Statement in SQL

This example shows a CASE statement within another CASE statement, also known as a “nested case statement” in SQL.

It first checks the country and then checks for a particular customer name to see if it is male or female (given that Sally is the only female here).

Notice how I didn’t give a name to the inner case statement. I didn’t need to – this is not displayed and the name is already specified for the Continent column.

SELECT first_name, last_name, country,
CASE
  WHEN country IN ('USA', 'Canada') THEN
    (CASE WHEN first_name = 'Sally' THEN 'North America F' ELSE 'North America M' END)
  WHEN country IN ('UK', 'France') THEN
    (CASE WHEN first_name = 'Sally' THEN 'Europe F' ELSE 'Europe M' END)
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;

FIRST_NAME	LAST_NAME	COUNTRY	CONTINENT
Adam	Cooper	USA	North America M
John	Smith	USA	North America M
Mark	Allan	UK	Europe M
Sally	Jones	USA	North America F
Steve	Brown	Canada	North America M

We can see that the results show different values based on the nested CASE statement.

CASE Statement with Functions

SELECT first_name, last_name, employees,
CASE
  WHEN MOD(employees, 2) = 0 THEN 'Even Number of Employees'
  WHEN MOD(employees, 2) = 1 THEN 'Odd Number of Employees'
  ELSE 'Unknown'
END OddOrEven
FROM customers
ORDER BY first_name, last_name;

FIRST_NAME	LAST_NAME	EMPLOYEES	ODDOREVEN
Adam	Cooper	55	Odd Number of Employees
John	Smith	4	Even Number of Employees
Mark	Allan	23	Odd Number of Employees
Sally	Jones	10	Even Number of Employees
Steve	Brown	15	Odd Number of Employees

This example uses the MOD function to demonstrate how you can use CASE statements with functions. It checks the number of employees and determines if they have an odd or even number of employees.

Multiple Matches

SELECT first_name, last_name, employees,
CASE
  WHEN employees < 1 THEN 'No Employees'
  WHEN employees < 10 THEN 'Small'
  WHEN employees <= 50 THEN 'Medium'
  WHEN employees >= 50 THEN 'Large'
END SizeOfCompany
FROM customers
ORDER BY first_name, last_name;

FIRST_NAME	LAST_NAME	EMPLOYEES	SIZEOFCOMPANY
Adam	Cooper	55	Large
John	Smith	4	Small
Mark	Allan	23	Medium
Sally	Jones	10	Medium
Steve	Brown	15	Medium

This example shows what happens if there are records that match with multiple WHEN expressions. For example, some customers may have both <1 employees and <10 employees. What happens here? The CASE statement finds the first matching expression and uses that.

CASE Statement in WHERE Clause

SELECT first_name, last_name, country
FROM customers
WHERE
  (CASE
    WHEN country IN ('USA', 'Canada') THEN 'North America'
    WHEN country IN ('UK', 'France') THEN 'Europe'
    ELSE 'Unknown'
  END) = 'North America'
ORDER BY first_name, last_name;

FIRST_NAME	LAST_NAME	COUNTRY
Adam	Cooper	USA
John	Smith	USA
Sally	Jones	USA
Steve	Brown	Canada

This example shows how the CASE statement is used in a WHERE clause.

This example shows all customers who live in North America, using the CASE statement to restrict the records.

CASE Statement Frequently Asked Questions

What’s an IF Statement?

In case you’re not sure, an IF statement allows you to do something if a condition is true, and something else if the condition is false.

It’s a common feature of many programming languages.

However, SQL isn’t like other programming languages.

It’s not procedural. It doesn’t make several steps on different variables to get the result you want.

It’s set based. You tell the database everything you want, and it returns a set of results.

So, how can you have an SQL IF statement?

I’ll be writing about how to write the IF statement in SQL.

What If No Match Is Found In A CASE Statement?

If there is no match found in any of the conditions, that’s where the ELSE statement comes in. The value used in the ELSE statement is what is returned if no match is found.

However, this is an optional part of the SQL CASE statement. If there is no result, and there is no ELSE statement, then the value of NULL is returned.

Does The CASE Statement Search All Conditions Or Just Finds The First Match?

A common question on SQL CASE statements is if the database evaluates all of the conditions in the CASE statement, or does it stop after finding the first match?

The answer is that it stops after the first match. It finds the first match, or the first expression that is evaluated to be a match, and does not continue with the rest.

It also performs something called “short-circuit evaluation” for Simple CASE expressions. This might not be a concern to you, but it’s good to know for performance reasons. The database will evaluate the first condition, then compare it to the expression, then evaluate the second condition, then evaluate that to the expression, and so on. It doesn’t evaluate all conditions before comparing the first one to the expression.

How Many Conditions or Arguments Can a CASE Statement Use?

The maximum number of conditions in a CASE statement is 255. This includes:

The initial expression in a simple CASE statement
The optional ELSE expression
The expression for the WHEN condition
The expression for the THEN result

You can use nested CASE statements so that the return value is a CASE expression. However, if you’re reaching the limit of 255 expressions, I would be looking at the efficiency of the query itself, as most queries should not need 255 expressions.

Can You Use An SQL CASE Statement In WHERE Clause?

Yes, you can use an SQL CASE in a WHERE clause. The examples below will show how this is done.

Can You Use An SQL CASE within CASE?

Yes, you can use a CASE within CASE in SQL. The examples below will show how this is done.

Can I Use DECODE Instead Of CASE?

Oracle has a function called DECODE, which lets you check an expression and return different values.

It looks like this:

DECODE (expression, condition1, result1, condition_n, result_n)

However, CASE is recommended for several reasons:

DECODE is older, and CASE was made as a replacement for DECODE.
CASE offers more flexibility.
CASE is easier to read.

In terms of performance, they are both very similar. Experiments have shown that unless you’re using millions of records, you won’t get much of a difference, and any difference will be small.

MySQL has a DECODE function but it’s used for something completely different. SQL Server and PostgreSQL don’t have a DECODE function.

There Is No IIF or IF in Oracle

SQL Server 2012 introduced a statement called IIF, which allows for an IF statement to be written.

However, Oracle does not have this functionality. It’s SQL Server only.

The CASE statement should let you do whatever you need with your conditions.

In Oracle, there is no “IF” statement or keyword specifically in Oracle. If you want to use IF logic, then use the CASE statement.

Procedural Languages Have an IF Statement

The procedural languages for each database do have an IF statement:

Oracle PL/SQL
SQL Server T-SQL
MySQL
PostgreSQL PGSQL

This statement works just like other languages. You have IF, ELSE, ELSIF and END.

However, that’s when you’re working with PL/SQL.

If you’re writing functions or stored procedures, you could use this IF statement.

If you’re just using standard SQL in your application or database, then you can use the CASE statement.

Conclusion

Hopefully, that explains how the SQL CASE statement is used and answers any questions you had. If you want to know more, just leave a comment below.

Источник

1. SQL CASE не всегда оценивается последовательно

2. Простое выражение SQL CASE оценивается многократно

Анализ

Эти три встроенные функции тайно преобразуются в SQL CASE

IIF

COALESCE

CHOOSE

На заметку

Question

Answers

Table of contents

SQL Server CASE Statement Forms

CASE expression Simple Form

CASE expression Searched Form

Comparison of Simple and Searched Forms SQL CASE

Use CASE to Compare a Range of Values

Data Transformation Example

Data Standardization Example

SQL CASE Prevents SQL Errors!

CASE in SELECT

CASE expression in ORDER BY

CASE expression in GROUP BY

CASE in WHERE Clause

CASE with CTE

Wrap Up

What Does the SQL CASE Statement Do?

Parameters of the CASE Statement

Simple and Searched CASE Expressions

Simple CASE expression

Searched CASE expression

Examples of the CASE Statement

Simple CASE Statement

Searched Case

Searched Case with Numbers

CASE Statement With IN Clause

Nested CASE Statement in SQL

CASE Statement with Functions

Multiple Matches

CASE Statement in WHERE Clause

CASE Statement Frequently Asked Questions

What’s an IF Statement?

What If No Match Is Found In A CASE Statement?

Does The CASE Statement Search All Conditions Or Just Finds The First Match?

How Many Conditions or Arguments Can a CASE Statement Use?

Can You Use An SQL CASE Statement In WHERE Clause?

Can You Use An SQL CASE within CASE?

Can I Use DECODE Instead Of CASE?

There Is No IIF or IF in Oracle

Procedural Languages Have an IF Statement

Conclusion