Para exemplificar o uso do CASE em uma consulta SQL, estarei mostrando aqui alguns exemplos que podem ser aplicados à situação a seguir.

Suponha que você tenha uma tabela de pessoas com os campos, cdcliente (código do cliente), cdfornecedor (código do fornecedor) e tppessoa (tipo de pessoa). Onde nesta tabela ou o campo cdcliente ou o campo cdfornecedor estará preenchido (nunca os dois ao mesmo tempo) . 

A idéia será fazer um sql que retorne o nome da pessoa dependendo do tipo.

Uma das soluções seria fazer dois left joins nas tabelas de cliente e fornecedor pegando o nome de cada uma delas. No entanto, neste caso a consulta retornaria dois campos, e isto não é desejado.

Para fazer então que a consulta retorne apenas um campo, utilizaremo o CASE.

Veja:

SELECT nome =
CASE tppessoa
   WHEN 1 THEN (
      SELECT c.nome FROM cliente c WHERE c.cdcliente = p.cdcliente
   )
   WHEN 2 THEN (
      SELECT f.nome FROM fornecedor f
      WHERE f.cdfornecedor = p.cdfornecedor
   )
   ELSE ''
END
FROM pessoa p

Neste exemplo, estamos verificando qual o valor contido no campo tppessoa. Caso for 1, fazemos um select na tabela de cliente buscando o seu nome. E caso for 2 buscamos o nome na tabela de fornecedores. O resultado e armazenado no campo nome, criado na própria consulta. Se nenhuma dos valores (1 ou 2) for verdadeiro o campo irá receber uma string vazia (neste caso).

Outra solução seria:

SELECT nome =
CASE
   WHEN cdfornecedor is not null THEN (
      SELECT c.nome FROM cliente c WHERE c.cdcliente = p.cdcliente
   )
   WHEN cdfornecedor is not null THEN (
      SELECT c.nome FROM cliente c WHERE c.cdcliente = p.cdcliente
   )
END
FROM pessoa p

Neste caso o campo tppessoa nem é necessário contanto que exista na tabela somente o valor de cdcliente ou cdfornecedor, sendo que um dos campos deverá conter valor nulo. Perceba que agora o CASE possui condições distintas, diferente do primeiro exemplo. Utilizando essa mesma idéia poderíamos fazer um select que retornaria a descrição do tipo de pessoa, por exemplo:

SELECT nmtppessoa =
   CASE
      WHEN cddependente is not null THEN 'Cliente'
      WHEN numatricula is not null THEN 'Fornecedor'
   END
FROM pessoa p

O CASE também pode ser usado em uma condição, ou seja dentro do WHERE. Veja um outro exemplo:

SELECT NOME , SALDO , SALDO_POUPANÇA , SALDO_APP , VAL_DEPOSITO
FROM CONTA_CORRENTE
WHERE
   (
   CASE SALDO
      WHEN SALDO < 0 THEN SALDO_POUPANÇA
      WHEN SALDO >= 0 THEN SALDO
      WHEN SALDO > 10000 THEN SALDO_APP
   ) > VAL_DEPOSITO

Neste exemplo, a condição sendo verdadeira, o que será retornado será o valor de um dos campos que logo após será verificado se é maior do que VAL_DEPOSITO, finalizando assim o WHERE da consulta principal.