Há momentos, no decorrer de um projeto, em que precisamos escrever uma única instrução T-SQL que seja capaz de retornar diferentes expressões com base em avaliações de outras expressões. É em casos como esse que podemos utilizar expressões do tipo CASE ou mesmo funções IIF para que nossas necessidades sejam atendidas. E será este o tema do nosso artigo. Iremos então ver a sintaxe CASE e IIF e apresentaremos alguns exemplos de como a expressão CASE e função IIF elas funcionam.
Expressão CASE
A expressão Transact-SQL CASE nos permite colocar a lógica condicional em nossos códigos T-SQL. Esta lógica condicional nos fornece uma maneira diferente de adição de blocos de código que podem ser executadas em nossas declarações T-SQL, dependendo de uma avaliação booleana da lógica condicional, nos dizendo se TRUE ou FALSE. Podemos, inclusive, colocar várias expressões condicionais em uma única expressão CASE. Para melhor compreendermos a forma como a expressão CASE realmente funciona, vamos rever a sintaxe da expressão CASE, apresentada pela Listagem 1, e em seguida, passaremos por uma série de exemplos diferentes que irão mostrar alguns conceitos e suas utilidades.
Para começarmos, precisamos observar que a expressão CASE possui dois formatos diferentes, um simples e o reviewed (revisto). Cada um destes tipos possui um formato ligeiramente diferente, ambos estão sendo apresentados na Listagem 1.
Listagem 1. Sintaxes da expressão CASE.
- Expressão Case simples:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
- Expressão Case Searched (busca):
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
ENDAo analisarmos os dois formatos apresentados para a expressão CASE, presentes na Listagem 1, podemos observar que cada um dos formatos oferece uma forma diferente de identificar uma das múltiplas expressões que determinam os resultados da expressão CASE. Em ambos os tipos de CASE, um teste booleano é realizado para cada uma das cláusulas WHEN. Após a palavra CASE, temos então o nosso termo de pesquisa, o qual é chamado de input_expression, que será verificado após o WHEN onde colocamos então a condição que deverá ser satisfeita. Numa expressão CASE simples do operador entre o "input_expression" e o "when_expression" é sempre o operador de igualdade. Enquanto que com a expressão CASE searched, cada cláusula WHEN irá conter um "Boolean_expression". Este "Boolean_expression" pode ser uma expressão booleana simples com um único operador, ou uma expressão booleana complexa, que pode apresentar várias condições diferentes. Além disso, a expressão CASE searched pode usar o conjunto completo de operadores booleanos.
Independentemente de qual formato CASE seja utilizado, cada cláusula WHEN é comparada na ordem em que aparece. O resultado da expressão CASE será baseado na primeira cláusula WHEN que é avaliada como TRUE. Se nenhuma cláusula WHEN for avaliada como TRUE, então a expressão ELSE é retornada. Quando a cláusula ELSE é omitida e nenhuma cláusula WHEN for avaliada como TRUE, em seguida, um valor NULL será retornado.
Exemplos de utilização de expressões CASE
Vamos então começar com alguns testes para esclarecer dúvidas com relação a utilização das expressões CASE. Para isso, criemos uma nova base de dados chamada de Testes e em seguida, vamos criar a nossa tabela de demonstrações, que será semelhante a apresentada pela Listagem 2, a qual será chamada de OrdemServico. Caso tenham interesse em acompanhar os testes de forma prática, criem então esta tabela em sua instância do SQL Server, no nosso caso, estamos utilizando o SQL Server 2014.
Listagem 2. Criando a Tabela OrdemServico.
CREATE TABLE OrdemServico (
ID int identity,
OrdemData date,
OrdemValor decimal(10,2),
Pago char(1));
INSERT into OrdemServico VALUES
('12-03-2014', 10.49, NULL),
('10-05-2014', 220.45,'S'),
('06-14-2012', 8.62, NULL),
('04-02-2012', 75.39, NULL),
('07-11-2013', 125.54, NULL),
('04-25-2008', 99.99, NULL),
('11-09-2013', 320.13, 'N'),
('04-05-2010', 175.76, NULL),
('03-27-2011', 1.69, NULL);Como estamos inserindo direto na base de dados os valores, devemos passar as datas e os valores monetários no formato americano, mas caso queiram fazer a mudança para formatos diferentes, fiquem a vontade. Sigamos agora para o nosso primeiro exemplo de utilização.
Usando uma expressão CASE simples com cláusulas WHEN e ELSE
Para demonstrarmos como funciona a expressão CASE simples, vamos executar primeiro o código presente na Listagem 3.
Listagem 3. Expressão CASE com ELSE.
use Testes;
SELECT YEAR(OrdemData) AS OrdemAno,
CASE YEAR(OrdemData)
WHEN 2014 THEN 'Ano 1'
WHEN 2013 THEN 'Ano 2'
WHEN 2012 THEN 'Ano 3'
ELSE 'Ano 4 e posteriores' END AS TipoAno
FROM OrdemServico;Se analisarmos o código apresentado pela Listagem 3 podemos observar que, logo após a palavra CASE, especificamos o ano como sendo "ANO (OrdemData)" e depois seguimos por mais outras cláusulas, onde cada uma possuía um ano diferente, começando com o ano de 2014.
Quando a nossa expressão CASE simples é avaliada, ela usa o operador de igualdade ("=") entre o "ANO (OrdemData)" e o Valor, e quando as expressões são diferentes. Portanto, o código da Listagem 3 irá exibir "Ano 1" para a coluna TipoAno que tenha linhas com um valor de ano OrdemData de "2014", ou então irá exibir "Ano 2" para linhas com um ano OrdemData de "2013" e assim por diante. Se o ano da OrdemData não corresponder a nenhuma das cláusulas passadas, a condição vai exibir "Ano 4 e posteriores". Quando executamos o código presente na Listagem 3, recebemos o resultado presente na Figura 1.
Figura 1. Resultado obtido pelo CASE simples.
Usando uma expressão simples CASE sem o ELSE
Neste exemplo, executaremos o código presente na Listagem 4, que irá nos mostrar o que acontece quando uma simples expressão CASE não possui uma cláusula ELSE.
Listagem 4. Expressão CASE sem uma cláusula ELSE.
use Testes;
SELECT YEAR(OrdemData) AS OrdemAno,
CASE YEAR(OrdemData)
WHEN 2014 THEN 'Ano 1'
WHEN 2013 THEN 'Ano 2'
WHEN 2012 THEN 'Ano 3' END AS TipoAno
FROM OrdemServico;O código presente na Listagem 4 é como o código apresentado na Listagem 3, mas com a diferença de não ter uma cláusula ELSE especificada. Neste caso, quando executamos o código da Listagem 4, este nos apresenta os resultados, como os mostrados pela Figura 2.
Figura 2. CASE sem a cláusula ELSE.
Ao analisarmos a saída apresentada pela Figura 2, podemos ver que quando o ano do OrdemData na tabela OrdemServico não cumpre com qualquer uma das condições para a cláusula WHEN, o SQL Server exibe "NULL" como valor para o TipoAno.
Usando uma expressão CASE Searched
A expressão simples do CASE com as cláusulas WHEN foi avaliada com base no operador de igualdade. Já no caso das expressões CASE do tipo searched, podemos fazer uso de outros operadores e a sintaxe da expressão CASE será um pouco diferente. Para que possamos demonstrar isso, vamos dar uma olhada no código apresentado pela Listagem 5.
Listagem 5. Expressão CASE do tipo searched.
use Testes;
SELECT YEAR(OrdemData) AS OrdemAno,
CASE
WHEN YEAR(OrdemData) = 2014 THEN 'Ano 1'
WHEN YEAR(OrdemData) = 2013 THEN 'Ano 2'
WHEN YEAR(OrdemData) = 2012 THEN 'Ano 3'
WHEN YEAR(OrdemData) < 2012 THEN 'Ano 4 e posteriores' END AS TipoAno
FROM OrdemServico;Se olharmos para o código apresentado na Listagem 5 podemos perceber que a cláusula WHEN vem diretamente após a expressão CASE sem nenhum texto entre as duas cláusulas. Isto diz ao SQL Server que esta expressão é um CASE searched (pesquisado). Além disso, observem a expressão booleana que está após cada cláusula WHEN. Como vocês podem ver, nem todas as expressões booleanas estão usando o operador de igualdade, a última expressão WHEN, por exemplo, usa o operador de menor que ("<"). A expressão CASE que apresentamos na Listagem 5 é logicamente o mesmo que a expressão CASE apresentada na Listagem 3. Por isso, quando executamos o código da Listagem 5, este produz os mesmos resultados, como mostrado pela Figura 1, apresentada anteriormente.
Múltiplas cláusulas, mesmo resultado
Podem haver situações em que diferentes cláusulas WHEN são avaliadas como verdadeiro dentro de uma única expressão CASE. Então, quando isso acontece, o SQL Server retorna o resultado da expressão associada ao primeiro WHEN que for avaliado como true. No entanto, a ordem de execução das cláusulas WHEN irá controlar o resultado obtido que retorna da expressão CASE quando múltiplas cláusulas WHEN são avaliadas como TRUE.
Para demonstrarmos como este processo ocorre, iremos usar a expressão CASE para exibir ordens de "500 Reais" quando o OrdemValor estiver dentro do limite dos R$ 500, "300 Reais" quando o OrdemValor estiver dentro da faixa da R$ 300 e "< 200 Reais" quando o OrdemValor for menor que R$ 200 e quando uma OrdemValor não estiver de acordo com nenhuma dessas categorias, então classificaremos a ordem como "Valores diferentes do especificado". Vamos ver então o código presente na Listagem 6 para demonstrarmos o que acontece quando múltiplas expressões WHEN são avaliadas como TRUE ao tentarmos categorizar as ordens em uma dessas categorias de valores Categoria_OrdemValor.
Listagem 6. Exemplo onde múltiplas expressões WHEN são avaliadas como TRUE.
use Testes;
SELECT OrdemValor,
CASE
WHEN OrdemValor < 500 THEN 'Abaixo de 500'
WHEN OrdemValor < 300 THEN 'Abaixo de 300'
WHEN OrdemValor < 200 THEN 'Valores menores que 200'
ELSE ' Valores diferentes do especificado.'
END AS Categoria_OrdemValor
FROM OrdemServico;Quando executamos o código presente na Listagem 6 recebemos a saída de acordo com a Figura 3.
Figura 3. Resultado da consulta.
Ao analisarmos os resultados da consulta, podemos ver que cada pedido é relatado como sendo abaixo de 500, e nós sabemos que isso é incorreto. Isso aconteceu porque só usamos o operador de menor que ("<") para classificarmos de forma simplista as Ordens que levam a múltiplas cláusulas WHEN como resultado para serem avaliadas como verdadeiras na expressão CASE. Porém, a ordenação das cláusulas WHEN não permitiu que a expressão correta fosse retornada. Reordenaremos então nossas cláusulas WHEN para que possamos obter os resultados da forma que desejamos. O código presente na Listagem 7 é o mesmo que o da Listagem 6, porém, aqui temos as cláusulas reordenadas para categorizar corretamente nossas ordens.
Listagem 7. Pesquisa reordenada para múltiplos resultados.
use Testes;
SELECT OrdemValor,
CASE
WHEN OrdemValor < 200 THEN 'Abaixo de 200'
WHEN OrdemValor < 300 THEN 'Abaixo de 300'
WHEN OrdemValor < 500 THEN 'Valores menores que 500'
ELSE ' Valores diferentes do especificado.'
END AS Categoria_OrdemValor
FROM OrdemServico;Quando executarmos o código da Listagem 7 receberemos a saída apresentada pela Figura 4.
Figura 4. Resultado ordenado da pesquisa.
Ao analisarmos a saída presente na Figura 4 podemos ver que, alterando a ordem da cláusula WHEN, obtemos os resultados de forma correta para cada ordem, como realmente deve ser.
Expressões CASE aninhadas
Ocasionalmente vocês podem ter a necessidade de executar testes adicionais para categorizar ainda mais dados usando a expressão CASE. Quando isso ocorre, vocês podem usar uma expressão CASE aninhada. O código presente na Listagem 8 mostra um exemplo de aninhamento para categorizar ainda mais as encomendas na tabela OrdemServico que é para determinar se a ordem foi emitida com o resultado Pago quando uma ordem é acima de R$120,00.
Listagem 8. Declarando um CASE aninhado.
use Testes;
SELECT OrdemValor,
CASE
WHEN OrdemValor < 200 THEN 'Ordem abaixo de 200 Reais'
WHEN OrdemValor < 300 THEN 'Ordem abaixo de 300 Reais'
WHEN OrdemValor < 500 THEN
CASE
WHEN Pago = 'S'
THEN 'Ordem acima de 300 Reais sem estar Pago'
ELSE 'Ordem de 300 Reais como Pago' END
ELSE
CASE
WHEN Pago = 'N'
THEN 'Ordem de 500 Reais sem estar como Pago'
ELSE 'Ordem acima de 500 Reais estando como Pago' END
END AS Categoria_OrdemValor
FROM OrdemServico;O código apresentado na Listagem 8 é semelhante ao que apresentamos na Listagem 7, a única diferença é que adicionamos uma expressão CASE a mais para ver se um determinado pedido na tabela OrdemServico foi comprado utilizando a opção Pago ou não, o que só é permitido em compras acima de 300 Reais. Tenham em mente que quando temos expressões CASE aninhadas, só são permitidos até 10 níveis de aninhamento no SQL Server.
Outros lugares onde a expressão CASE pode ser utilizada
Até agora, todos os nossos exemplos usaram expressões CASE para criar uma string de resultados, colocando a expressão CASE na lista de seleção de uma instrução SELECT do T-SQL. Também podemos usar uma expressão CASE em instruções de UPDATE, DELETE e instruções do tipo SET. Além disso, a expressão CASE pode ser usada em conjunto com IN, WHERE, ORDER BY e HAVING. Na Listagem 9 estaremos usando uma expressão CASE com uma cláusula WHERE.
Listagem 9. CASE com a clausula WHERE.
use Testes;
SELECT *
FROM OrdemServico
WHERE CASE YEAR(OrdemData)
WHEN 2014 THEN 'Ano 1'
WHEN 2013 THEN 'Ano 2'
WHEN 2012 THEN 'Ano 3'
ELSE 'Ano 4 e posteriores' END = 'Ano 1';Na Listagem 9 queremos apenas retornar uma ordem da tabela de OrdemServico para linhas apresentando "Ano 1". Para conseguirmos isso, colocamos a mesma expressão CASE como foi utilizada na Listagem 3 na cláusula WHERE. Usamos então a expressão CASE como a parte do lado esquerdo da condição WHERE para que ele produza uma sequência diferente de "Ano ..." com base na coluna OrdemData. Testamos então a string que foi produzida a partir da expressão CASE para ver se era igual ao valor "Ano 1", em caso positivo, uma linha seria devolvida a partir da tabela OrdemServico. Não recomendamos usar uma expressão CASE para selecionar a data a partir de uma coluna de data dessa forma, pois existem melhores métodos para isso, como por exemplo, usar a função YEAR para selecionarmos as linhas de um determinado ano. Fizemos dessa forma apenas para demonstrar como usar uma instrução CASE com cláusulas WHERE.
Expressões CASE com a função IIF
A partir da versão 2012 do SQL Server, a Microsoft adicionou a função IIF. A função IIF pode ser considerado um atalho para a instrução CASE. Na Listagem 10 podemos encontrar a sintaxe para a função IIF.
Listagem 10. Sintaxe da expressão IIF.
IIF ( boolean_expression, true_value, false_value )O "Boolean_expression" é uma expressão booleana válida que equivale a VERDADEIRO ou FALSO. Quando a expressão booleana é igual a um valor VERDADEIRO, em seguida, a expressão "true_value" é executada. Se a expressão booleana equivale a FALSE então o "false_value" é executado. Assim como a expressão CASE, a função IIF pode ser aninhada até 10 níveis.
Para demonstrarmos como a função IIF pode ser usada para substituir a expressão CASE, vamos rever o código no qual usamos a expressão CASE searched presente na Listagem 11.
Listagem 11. Exemplo simples de utilização da expressão CASE.
use Testes;
SELECT OrdemValor,
CASE
WHEN OrdemValor > 200 THEN 'Acima do valor'
ELSE 'Abaixo do valor' END AS OrdemType
FROM OrdemServico;O código presente na Listagem 11 possui apenas uma única expressão WHEN que é utilizada para determinar se o OrdemValor é uma ordem de alta ou baixa do valor especificado. Se a expressão WHEN "OrdemValor > 200" for avaliada como TRUE, então o valor OrdemType está definido para "Acima do valor". Se a expressão WHEN é avaliada como FALSE então "Abaixo do valor" será definido para o valor OrdemType.
O código reescrito da função que usamos em vez de uma expressão CASE IIF pode ser encontrado presente na Listagem 12.
Listagem 12. Exemplo utilizando a expressão IIF.
use Testes;
SELECT OrdemValor,
IIF(OrdemValor > 200,
'Acima do valor',
'Abaixo do valor') AS OrdemTipe
FROM OrdemServico;Ao olharmos para a Listagem 12 podemos ver o porquê da função IIF ser considerada uma versão abreviada da expressão CASE. A palavra CASE é substituída pelo "IIF ("string, a cláusula "então" é substituída por uma vírgula, a cláusula "senão" é substituído por uma vírgula e a palavra "END" é substituída por um ")". Quando a expressão booleana "OrdemValor > 200". É verdade que o valor " Acima do valor " é exibido. Quando a expressão booleana 'OrdemValor > 200 " é avaliada como FALSE então o" Abaixo do valor" é exibido. Se executarmos o código presente nas Listagem 11 e 12, veremos que ambos produzem o mesmo resultado.
Aninhamento com a função IIF
Assim como a expressão CASE do SQL Server permite que aninhemos funções IIF. Na Listagem 13 temos um exemplo de aninhamento com a função IIF.
Listagem 13. Exemplo da função IIF aninhada.
use Testes;
SELECT OrdemValor,
IIF (OrdemValor < 130,
'Abaixo de 130',
(IIF (OrdemValor < 200,
'Abaixo de 200',
(IIF (OrdemValor < 300,
(IIF (Pago = 'N',
'Ordem de 200 Sem ser como Pago',
'Ordem de 200 como Pago'
)
),
(IIF (Pago = 'N',
'Ordem de 300 Sem ser como Pago',
'Ordem de 300 como Pago'
)
)
)
)
)
)
) AS Categoria_OrdemValor
FROM OrdemServico;Neste exemplo podemos ver que usamos a função IIF várias vezes. Cada um adicional é usado tanto no "valor verdadeiro" quanto no "valor falso" da função IIF. O código presente na Listagem 13 é equivalente ao código que usa a expressão CASE aninhada presente na Listagem 8.
Limitações quanto ao uso das expressões CASE e IIF
Assim como ocorre com a maioria das funcionalidades presentes no T-SQL, existem limitações quanto ao uso das mesmas. Abaixo estão algumas limitações a serem observadas em relação ao uso das funções IIF e expressões CASEs.
Limitações para a expressão CASE
- Podemos apenas ter até 10 níveis de aninhamento em suas expressões.
- Expressões CASE não podem ser utilizadas para controlar o fluxo de execução de instruções T-SQL.
Limitação da função IIF:
- Podemos ter até 10 níveis de aninhamento de cláusulas IIF.
Com isso finalizamos mais este artigo sobre T-SQL, onde mostramos que a expressão CASE e a função IIF nos permitem colocar a expressão lógica no código T-SQL que vai mudar os resultados de nosso código com base nos resultados avaliados de uma expressão. Usando a expressão de comparação suportada pela função IIF e a expressão CASE podem ter diferentes blocos de códigos executados, dependendo se a expressão de comparação é avaliada como VERDADEIRO ou FALSO. A expressão CASE e a função IIF nos fornecem o controle programático para atender aos requisitos do nosso negócio de forma mais simples e de fácil utilização.
Até a próxima!
