Trabalhando com expressões CASE e a Função IIF no T-SQL.pdf

8/18/2019 Trabalhando com expressões CASE e a Função IIF no T-SQL.pdf

1/18

comentários post favorito (2)

16CurtirCurtir1

CONTEÚDO REVISTAS CURSOS APIs MVP LOGIN

Anuncie | Loja | Publique | Assine | Fale conosco

Hospedagem web por Porta 80 Web Todos os Direitos Reservados a

DevMedia

31.203 pessoas curtiram DevMedia.

Plug-in social do Facebook

CurtirCurtir

balhando com expressões CASE e a Função IIF no T-SQL http://www.devmedia.com.br/trabalhando-com-expressoes-case-e-a-fu...

18 20/09/2014 19:27


2/18

A expressão Transact-SQL CASE nos permite colocar a lóica con!icional em nossos có!ios

T-SQL" Esta lóica con!icional nos fornece #ma maneira !iferente !e a!i$ão !e %locos !e

có!io e po!em ser exec#ta!as em nossas !eclara$'es T-SQL !epen!en!o !e #ma

avalia$ão %ooleana !a lóica con!icional nos !ien!o se T*+E o# ,ALSE" o!emos incl#sive

colocar várias express'es con!icionais em #ma .nica expressão CASE" ara mel/or

compreen!ermos a forma como a expressão CASE realmente f#nciona vamos rever a sintaxe

!a expressão CASE apresenta!a pela Listagem 1 e em se#i!a passaremos por #ma s0rie

!e exemplos !iferentes e irão mostrar al#ns conceitos e s#as #tili!a!es"

ara come$armos precisamos o%servar e a expressão CASE poss#i !ois formatos

!iferentes #m simples e o reviee! (revisto)" Ca!a #m !estes tipos poss#i #m formato

lieiramente !iferente am%os estão sen!o apresenta!os na Listagem 1"

Listagem 1" Sintaxes !a 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 ]

END

Ao analisarmos os !ois formatos apresenta!os para a expressão CASE presentes na

Listagem 1 po!emos o%servar e ca!a #m !os formatos oferece #ma forma !iferente !e

i!entificar #ma !as m.ltiplas express'es e !eterminam os res#lta!os !a expressão CASE"

Em am%os os tipos !e CASE #m teste %ooleano 0 realia!o para ca!a #ma !as clá#s#las

3E4" Após a palavra CASE temos então o nosso termo !e pesisa o al 0 c/ama!o !e

inp#t5expression e será verifica!o após o 3E4 on!e colocamos então a con!i$ão e

!everá ser satisfeita" 4#ma expressão CASE simples !o opera!or entre o 6inp#t5expression6e o 6/en5expression6 0 sempre o opera!or !e i#al!a!e" Enanto e com a expressão

CASE searc/e! ca!a clá#s#la 3E4 irá conter #m 67oolean5expression6" Este



18 20/09/2014 19:27


3/18

#ma expressão %ooleana complexa e po!e apresentar várias con!i$'es !iferentes" Al0m

!isso a expressão CASE searc/e! po!e #sar o con8#nto completo !e opera!ores %ooleanos"

9n!epen!entemente !e al formato CASE se8a #tilia!o ca!a clá#s#la 3E4 0 compara!a

na or!em em e aparece" : res#lta!o !a expressão CASE será %asea!o na primeira clá#s#la

3E4 e 0 avalia!a como T*+E" Se nen/#ma clá#s#la 3E4 for avalia!a como T*+E

então a expressão ELSE 0 retorna!a" Q#an!o a clá#s#la ELSE 0 omiti!a e nen/#ma clá#s#la

3E4 for avalia!a como T*+E em se#i!a #m valor 4+LL será retorna!o"

;amos então come$ar com al#ns testes para esclarecer !.vi!as com rela$ão a #tilia$ão !as

express'es CASE" ara isso criemos #ma nova %ase !e !a!os c/ama!a !e Testes e em

se#i!a vamos criar a nossa ta%ela !e !emonstra$'es e será semel/ante a apresenta!a

pela Listagem 2 a al será c/ama!a !e :r!emServico" Caso ten/am interesse em

acompan/ar os testes !e forma prática criem então esta ta%ela em s#a inst"

Listagem 2" Crian!o a Ta%ela :r!emServico"

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 inserin!o !ireto na %ase !e !a!os os valores !evemos passar as !atas e os

valores monetários no formato americano mas caso eiram faer a m#!an$a para formatos

!iferentes fiem a vonta!e" Siamos aora para o nosso primeiro exemplo !e #tilia$ão"



18 20/09/2014 19:27


4/18

ara !emonstrarmos como f#nciona a expressão CASE simples vamos exec#tar primeiro o

có!io 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'



ELSE 'Ano 4 e posteriores' END AS TipoAno

FROM OrdemServico;

Se analisarmos o có!io apresenta!o pela Listagem 3 po!emos o%servar e loo após a

palavra CASE especificamos o ano como sen!o 6A4: (:r!em?ata)6 e !epois se#imos por

mais o#tras clá#s#las on!e ca!a #ma poss#@a #m ano !iferente come$an!o com o ano !e

2=1>"

Q#an!o a nossa expressão CASE simples 0 avalia!a ela #sa o opera!or !e i#al!a!e (66)

entre o 6A4: (:r!em?ata)6 e o ;alor e an!o as express'es são !iferentes" ortanto o

có!io !a Listagem 3 irá exi%ir 6Ano 16 para a col#na TipoAno e ten/a lin/as com #m

valor !e ano :r!em?ata !e 62=1>6 o# então irá exi%ir 6Ano 26 para lin/as com #m ano

:r!em?ata !e 62=1B6 e assim por !iante" Se o ano !a :r!em?ata não correspon!er a

nen/#ma !as clá#s#las passa!as a con!i$ão vai exi%ir 6Ano > e posteriores6" Q#an!o

exec#tamos o có!io presente na Listagem 3 rece%emos o res#lta!o presente na Figura 1"



18 20/09/2014 19:27


5/18

Figura 1" *es#lta!o o%ti!o pelo CASE simples"

4este exemplo exec#taremos o có!io presente na Listagem 4 e irá nos mostrar o e

acontece an!o #ma simples expressão CASE não poss#i #ma clá#s#la ELSE"

Listagem 4" Expressão CASE sem #ma clá#s#la ELSE"

use Testes;


CASE YEAR(OrdemData)



WHEN 2012 THEN 'Ano 3' END AS TipoAno

FROM OrdemServico;



18 20/09/2014 19:27


6/18

!iferen$a !e não ter #ma clá#s#la ELSE especifica!a" 4este caso an!o exec#tamos o

có!io !a Listagem 4 este nos apresenta os res#lta!os como os mostra!os pela Figura 2"

Figura 2" CASE sem a clá#s#la ELSE"



18 20/09/2014 19:27


7/18

:r!em?ata na ta%ela :r!emServico não c#mpre com aler #ma !as con!i$'es para a

clá#s#la 3E4 o SQL Server exi%e 64+LL6 como valor para o TipoAno"

A expressão simples !o CASE com as clá#s#las 3E4 foi avalia!a com %ase no opera!or !e

i#al!a!e" á no caso !as express'es CASE !o tipo searc/e! po!emos faer #so !e o#tros

opera!ores e a sintaxe !a expressão CASE será #m po#co !iferente" ara e possamos

!emonstrar isso vamos !ar #ma ol/a!a no có!io apresenta!o pela Listagem 5"

Listagem 5" Expressão CASE !o tipo searc/e!"

use Testes;


CASE

WHEN YEAR(OrdemData) = 2014 THEN 'Ano 1'



WHEN YEAR(OrdemData) < 2012 THEN 'Ano 4 e posteri

FROM OrdemServico;

Se ol/armos para o có!io apresenta!o na Listagem 5 po!emos perce%er e a clá#s#la

3E4 vem !iretamente após a expressão CASE sem nen/#m texto entre as !#as clá#s#las"

9sto !i ao SQL Server e esta expressão 0 #m CASE searc/e! (pesisa!o)" Al0m !isso

o%servem a expressão %ooleana e está após ca!a clá#s#la 3E4" Como vocDs po!em ver

nem to!as as express'es %ooleanas estão #san!o o opera!or !e i#al!a!e a .ltima

expressão 3E4 por exemplo #sa o opera!or !e menor e (66)" A expressão CASE e

apresentamos na Listagem 5 0 loicamente o mesmo e a expressão CASE apresenta!a na

Listagem 3" or isso an!o exec#tamos o có!io !a Listagem 5, este pro!# os mesmosres#lta!os como mostra!o pela Figura 1, apresenta!a anteriormente"

o!em /aver sit#a$'es em e !iferentes clá#s#las 3E4 são avalia!as como ver!a!eiro

!entro !e #ma .nica expressão CASE" Então an!o isso acontece o SQL Server retorna o

res#lta!o !a expressão associa!a ao primeiro 3E4 e for avalia!o como true" 4o entantoa or!em !e exec#$ão !as clá#s#las 3E4 irá controlar o res#lta!o o%ti!o e retorna !a

expressão CASE an!o m.ltiplas clá#s#las 3E4 são avalia!as como T*+E"



18 20/09/2014 19:27


8/18

or!ens !e 6F== *eais6 an!o o :r!em;alor estiver !entro !o limite !os *G F== 6B== *eais6

an!o o :r!em;alor estiver !entro !a faixa !a *G B== e 6 2== *eais6 an!o o

:r!em;alor for menor e *G 2== e an!o #ma :r!em;alor não estiver !e acor!o com

nen/#ma !essas cateorias então classificaremos a or!em como 6;alores !iferentes !o

especifica!o6" ;amos ver então o có!io presente na Listagem 6 para !emonstrarmos o e

acontece an!o m.ltiplas express'es 3E4 são avalia!as como T*+E ao tentarmos

cateoriar as or!ens em #ma !essas cateorias !e valores Cateoria5:r!em;alor"

Listagem 6" Exemplo on!e m.ltiplas express'es 3E4 são avalia!as como T*+E"

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 2

ELSE ' Valores diferentes do especificado.'

END AS Categoria_OrdemValor

FROM OrdemServico;

Q#an!o exec#tamos o có!io presente na Listagem 6 rece%emos a sa@!a !e acor!o com a

Figura 3"



18 20/09/2014 19:27


9/18

Figura 3" *es#lta!o !a cons#lta"

Ao analisarmos os res#lta!os !a cons#lta po!emos ver e ca!a pe!i!o 0 relata!o como

sen!o a%aixo !e F== e nós sa%emos e isso 0 incorreto" 9sso acontece# pore só #samos

o opera!or !e menor e (66) para classificarmos !e forma simplista as :r!ens e levam a

m.ltiplas clá#s#las 3E4 como res#lta!o para serem avalia!as como ver!a!eiras na

expressão CASE" or0m a or!ena$ão !as clá#s#las 3E4 não permiti# e a expressão

correta fosse retorna!a" *eor!enaremos então nossas clá#s#las 3E4 para e possamoso%ter os res#lta!os !a forma e !ese8amos" : có!io presente na Listagem 7 0 o mesmo

e o !a Listagem 6 por0m ai temos as clá#s#las reor!ena!as para cateoriar

corretamente nossas or!ens"

Listagem 7" esisa reor!ena!a para m.ltiplos res#lta!os.

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 5



18 20/09/2014 19:27


10/18

FROM OrdemServico;

Q#an!o exec#tarmos o có!io !a Listagem 7 rece%eremos a sa@!a apresenta!a pela Figura

4"

Figura 4" *es#lta!o or!ena!o !a pesisa"

Ao analisarmos a sa@!a presente na Figura 4 po!emos ver e alteran!o a or!em !a

clá#s#la 3E4 o%temos os res#lta!os !e forma correta para ca!a or!em como realmente

!eve ser"

:casionalmente vocDs po!em ter a necessi!a!e !e exec#tar testes a!icionais para

cateoriar ain!a mais !a!os #san!o a expressão CASE" Q#an!o isso ocorre vocDs po!em



de 18 20/09/2014 19:27


11/18

!e anin/amento para cateoriar ain!a mais as encomen!as na ta%ela :r!emServico e 0

para !eterminar se a or!em foi emiti!a com o res#lta!o ao an!o #ma or!em 0 acima !e

*G12==="

Listagem 8" ?eclaran!o #m CASE anin/a!o"

use Testes;

SELECT OrdemValor,

CASE

WHEN OrdemValor < 200 THEN 'Ordem abaixo de 200 R

WHEN OrdemValor < 300 THEN 'Ordem abaixo de 300 R

WHEN OrdemValor < 500 THEN

CASE

WHEN Pago = 'S'

THEN 'Ordem aciELSE 'Ordem de

ELSE

CASE

WHEN Pago = 'N'

THEN 'Ordem de

ELSE 'Ordem aci

END AS Categoria_OrdemValor

FROM OrdemServico;

: có!io apresenta!o na Listagem 8 0 semel/ante ao e apresentamos na Listagem 7 a

.nica !iferen$a 0 e a!icionamos #ma expressão CASE a mais para ver se #m !etermina!o

pe!i!o na ta%ela :r!emServico foi compra!o #tilian!o a op$ão ao o# não o e só 0

permiti!o em compras acima !e B== *eais" Ten/am em mente e an!o temos express'es

CASE anin/a!as só são permiti!os at0 1= n@veis !e anin/amento no SQL Server"

At0 aora to!os os nossos exemplos #saram express'es CASE para criar #ma strin !e

res#lta!os colocan!o a expressão CASE na lista !e sele$ão !e #ma instr#$ão SELECT !o

T-SQL" Tam%0m po!emos #sar #ma expressão CASE em instr#$'es !e +?ATE ?ELETE e

instr#$'es !o tipo SET" Al0m !isso a expressão CASE po!e ser #sa!a em con8#nto com 94

3E*E :*?E* 7H e 3A;94I" 4a Listagem 9 estaremos #san!o #ma expressão CASE com

#ma clá#s#la 3E*E"

Listagem 9" CASE com a cla#s#la 3E*E"



e 18 20/09/2014 19:27


12/18

SELECT *

FROM OrdemServico

WHERE CASE YEAR(OrdemData)




ELSE 'Ano 4 e posteriores' END = 'Ano 1';

4a Listagem 9 eremos apenas retornar #ma or!em !a ta%ela !e :r!emServico para

lin/as apresentan!o 6Ano 16" ara conse#irmos isso colocamos a mesma expressão CASE

como foi #tilia!a na Listagem 3 na clá#s#la 3E*E" +samos então a expressão CASE como

a parte !o la!o eser!o !a con!i$ão 3E*E para e ele pro!#a #ma seDncia !iferente

!e 6Ano """6 com %ase na col#na :r!em?ata" Testamos então a strin e foi pro!#i!a a

partir !a expressão CASE para ver se era i#al ao valor 6Ano 16 em caso positivo #ma lin/a

seria !evolvi!a a partir !a ta%ela :r!emServico" 4ão recomen!amos #sar #ma expressão

CASE para selecionar a !ata a partir !e #ma col#na !e !ata !essa forma pois existem

mel/ores m0to!os para isso como por exemplo #sar a f#n$ão HEA* para selecionarmos as

lin/as !e #m !etermina!o ano" ,iemos !essa forma apenas para !emonstrar como #sar #ma

instr#$ão CASE com clá#s#las 3E*E"

A partir !a versão 2=12 !o SQL Server a Jicrosoft a!iciono# a f#n$ão 99," A f#n$ão 99, po!e

ser consi!era!o #m atal/o para a instr#$ão CASE" 4a Listagem 10 po!emos encontrar a

sintaxe para a f#n$ão 99,"

Listagem 10" Sintaxe !a expressão 99,"

IIF ( boolean_expression, true_value, false_value )

: 67oolean5expression6 0 #ma expressão %ooleana váli!a e eivale a ;E*?A?E9*: o#

,ALS:" Q#an!o a expressão %ooleana 0 i#al a #m valor ;E*?A?E9*: em se#i!a a

expressão 6tr#e5val#e6 0 exec#ta!a" Se a expressão %ooleana eivale a ,ALSE então o

6false5val#e6 0 exec#ta!o" Assim como a expressão CASE a f#n$ão 99, po!e ser anin/a!a at0

1= n@veis"

ara !emonstrarmos como a f#n$ão 99, po!e ser #sa!a para s#%stit#ir a expressão CASE

vamos rever o có!io no al #samos a expressão CASE searc/e! presente na Listagem 11"



de 18 20/09/2014 19:27


13/18

use Testes;

SELECT OrdemValor,

CASE

WHEN OrdemValor > 200 THEN 'Acima do valor'

ELSE 'Abaixo do valor' END AS OrdemType

FROM OrdemServico;

: có!io presente na Listagem 11 poss#i apenas #ma .nica expressão 3E4 e 0

#tilia!a para !eterminar se o :r!em;alor 0 #ma or!em !e alta o# %aixa !o valor

especifica!o" Se a expressão 3E4 6:r!em;alor K 2==6 for avalia!a como T*+E então o

valor :r!emTpe está !efini!o para 6Acima !o valor6" Se a expressão 3E4 0 avalia!a como

,ALSE então 6A%aixo !o valor6 será !efini!o para o valor :r!emTpe"

: có!io reescrito !a f#n$ão e #samos em ve !e #ma expressão CASE 99, po!e ser

encontra!o presente na Listagem 12"

Listagem 12" Exemplo #tilian!o a expressão 99,"

use Testes;

SELECT OrdemValor,

IIF(OrdemValor > 200, 'Acima do valor',

'Abaixo do valor') AS OrdemTipe

FROM OrdemServico;

Ao ol/armos para a Listagem 12 po!emos ver o porD !a f#n$ão 99, ser consi!era!a #ma

versão a%revia!a !a expressão CASE" A palavra CASE 0 s#%stit#@!a pelo 699, (6strin a

clá#s#la 6então6 0 s#%stit#@!a por #ma v@r#la a clá#s#la 6senão6 0 s#%stit#@!o por #ma

v@r#la e a palavra 6E4?6 0 s#%stit#@!a por #m 6)6" Q#an!o a expressão %ooleana

6:r!em;alor K 2==6" M ver!a!e e o valor 6 Acima !o valor 6 0 exi%i!o" Q#an!o a expressão

%ooleana N:r!em;alor K 2== 6 0 avalia!a como ,ALSE então o6 A%aixo !o valor6 0 exi%i!o" Se

exec#tarmos o có!io presente nas Listagem 11 e 12 veremos e am%os pro!#em o

mesmo res#lta!o"

Assim como a expressão CASE !o SQL Server permite e anin/emos f#n$'es 99," 4a

Listagem 13 temos #m exemplo !e anin/amento com a f#n$ão 99,"



de 18 20/09/2014 19:27


14/18

use Testes;

SELECT OrdemValor,

IIF (OrdemValor < 130,

'Abaixo de 130',

(IIF (OrdemValor < 200,

'Abaixo de 200',

(IIF (OrdemValor < 300,

(IIF (Pago = 'N',

'Ordem

'Ordem

)

),

(IIF (Pago =

'Ordem

'Ordem)

)

)

)

)

)

) AS Categoria_OrdemValor

FROM OrdemServico;

4este exemplo po!emos ver e #samos a f#n$ão 99, várias vees" Ca!a #m a!icional 0

#sa!o tanto no 6valor ver!a!eiro6 anto no 6valor falso6 !a f#n$ão 99," : có!io presente na

Listagem 13 0 eivalente ao có!io e #sa a expressão CASE anin/a!a presente na

Listagem 8"

Assim como ocorre com a maioria !as f#ncionali!a!es presentes no T-SQL existem limita$'es

anto ao #so !as mesmas" A%aixo estão al#mas limita$'es a serem o%serva!as em rela$ão

ao #so !as f#n$'es 99, e express'es CASEs"

Limita$'es para a expressão CASE

o!emos apenas ter at0 1= n@veis !e anin/amento em s#as express'es"

Express'es CASE não po!em ser #tilia!as para controlar o fl#xo !e exec#$ão !einstr#$'es T-SQL"

Limita$ão !a f#n$ão 99,O



de 18 20/09/2014 19:27


15/18

: e vocD ac/o# !este postP

Iostei (1) (=)

Com isso finaliamos mais este artio so%re T-SQL on!e mostramos e a expressão CASE e

a f#n$ão 99, nos permitem colocar a expressão lóica no có!io T-SQL e vai m#!ar os

res#lta!os !e nosso có!io com %ase nos res#lta!os avalia!os !e #ma expressão" +san!o a

expressão !e compara$ão s#porta!a pela f#n$ão 99, e a expressão CASE po!em ter

!iferentes %locos !e có!ios exec#ta!os !epen!en!o se a expressão !e compara$ão 0

avalia!a como ;E*?A?E9*: o# ,ALS:" A expressão CASE e a f#n$ão 99, nos fornecem o

controle proramático para aten!er aos reisitos !o nosso neócio !e forma mais simples e

!e fácil #tilia$ão"

At0 a próxima

Edson José Dionisio

Graduado em Sistemas de informação pela Faculdade Joaquim Nabuco - Recife - PE. Pós - graduando em Banco de

dados Oracle pela Faculdade Maurício de Nassau - Recife. Trabalho com desenvolvimento de sites e sistemas em

PHP, utiliza [...]

Comentário R Tire s#a !.vi!a

Publicidade



de 18 20/09/2014 19:27


16/18

Servi$os

9ncl#a #m comentário

A!icionar aos ,avoritos

Jarcar como li!oassisti!o

9ncl#ir anota$ão pessoal;ersão para impressão

7anco !e!a!os



de 18 20/09/2014 19:27


17/18



de 18 20/09/2014 19:27


18/18



Documents

Trabalhando com expressões CASE e a Função IIF no T-SQL.pdf