Função Oracle CASE na linguagem SQL

• Descrição:
  

Esta função permite a aplicação da lógica if-then-else em interrogações SQL.

É possível utilizar esta função no select, where, group by, having, order by de uma interrogação.

Configurações possíveis:

CASE "Expressão"
WHEN "valor a testar" THEN
          "valor retornar"
WHEN "valor a testar" THEN
          "valor retornar" 
ELSE
          "valor a retornar"
END
 
ou 
 
CASE
WHEN "condição a avaliar" THEN
          "valor retornar" 
WHEN "condição a avaliar" THEN
          "Valor retornar"
ELSE
          "Valor a retornar"
END
 
Configuração mínima:
 
CASE 
WHEN "condição a avaliar" THEN
          "valor retornar"
END

• Erros:
  

Quando os valores a retornar pelo CASE não são no mesmo tipo ocorre o erro:

"ORA-00932: inconsistent datatypes: expected .... got ......"

• Exemplos:
  

Criar a tabela:

CREATE TABLE ohb_artigos
(artigo_id NUMBER,
artigo_titulo_txt VARCHAR2(200),
artigo_descricao_txt VARCHAR2(4000),
artigo_activo_indc NUMBER(1,0),
artigo_autor_txt VARCHAR2(20),
insercao_data DATE);

Inserir os dados:

INSERT INTO ohb_artigos
("ARTIGO_ID","ARTIGO_TITULO_TXT","ARTIGO_DESCRICAO_TXT","ARTIGO_ACTIVO_INDC","ARTIGO_AUTOR_TXT","INSERCAO_DATA")
VALUES

(1,'Desmistificar comportamentos: NOT IN',NULL,1,'Adriano Teixeira ','7-11-2007 00:00:00')
/

INSERT INTO ohb_artigos
("ARTIGO_ID","ARTIGO_TITULO_TXT","ARTIGO_DESCRICAO_TXT","ARTIGO_ACTIVO_INDC","ARTIGO_AUTOR_TXT","INSERCAO_DATA")
VALUES
(2,'Reset de sequências',NULL,1,'Adriano Teixeira ','7-30-2007 00:00:00')
/

INSERT INTO ohb_artigos
("ARTIGO_ID","ARTIGO_TITULO_TXT","ARTIGO_DESCRICAO_TXT","ARTIGO_ACTIVO_INDC","ARTIGO_AUTOR_TXT","INSERCAO_DATA")
VALUES
(3,'Como começou... ',NULL,1,'Adriano Teixeira ','7-10-2007 00:00:00')
/

INSERT INTO ohb_artigos
("ARTIGO_ID","ARTIGO_TITULO_TXT","ARTIGO_DESCRICAO_TXT","ARTIGO_ACTIVO_INDC","ARTIGO_AUTOR_TXT","INSERCAO_DATA")
VALUES
(4,'Funções na linguagem Oracle SQL',NULL,0,'MNunes','7-12-2007 00:00:00')
/

INSERT INTO ohb_artigos
("ARTIGO_ID","ARTIGO_TITULO_TXT","ARTIGO_DESCRICAO_TXT","ARTIGO_ACTIVO_INDC","ARTIGO_AUTOR_TXT","INSERCAO_DATA")
VALUES
(5,'Função Oracle CASE ',NULL,0,'MNunes','8-26-2007 00:00:00')
/

Exemplo nº1: Obter os títulos dos artigos e os respectivos estados com as seguintes descrições: 'Artigo activo', 'Artigo não activo', 'Artigo censurado'

SELECT oa.artigo_titulo_txt "Titulo", case artigo_activo_indc

                                    when 1 then

                                    'Artigo activo'

                                    when 0 then

                                    'Artigo não activo'

                                    else

                                    'Artigo censurado'

                                    End artigo_estado_txt "Estado"

  FROM ohb_artigos oa;

Resultado:

Titulo                                                                     Estado
Desmistificar comportamentos: NOT IN                  Artigo activo
Reset de sequências                                             Artigo activo
Como começou...                                                   Artigo activo
Funções na linguagem Oracle SQL                       Artigo não activo
Função Oracle CASE                                            Artigo não activo

Exemplo nº2: Obter os títulos dos artigos e respectivos autores no formato: Titulo - Autor. Caso o artigo não esteja activo assinalar.

select oa.artigo_titulo_txt' - 'oa.artigo_autor_txtcase
                                                           when (artigo_activo_indc<>1)then
                                                            '(Artigo não activo ou censurado)'
                                                       End "Titulo - Autor"
FROM ohb_artigos oa; 

Resultado:

"Titulo - Autor"

Desmistificar comportamentos: NOT IN - Adriano Teixeira
Reset de sequências - Adriano Teixeira
Como começou...  - Adriano Teixeira
Funções na linguagem Oracle SQL - MNunes(Artigo não activo ou censurado)
Função Oracle CASE  - MNunes(Artigo não activo ou censurado)

Exemplo nº3: Ano ou mês ou dia com artigos activos e respectivo nº.
No caso comentei o parâmetro p_tipo_agregacao e coloquei 'MES'.

select case 'MES' --p_tipo_agregacao

              when 'ANO' then

                  TO_CHAR(oa.insercao_data,'RRRR')

              when 'MES' then

                  TO_CHAR(oa.insercao_data,'RRRR-MM')

              else

                  TO_CHAR(oa.insercao_data,'RRRR-MM-DD')

         End "Intervalo",

         count(case artigo_activo_indc

                   when 1 then

                    1

                   else

                   null

                End

               ) "nº artigos activos"

from ohb_artigos oa

group by case 'MES' --p_tipo_agregacao

              when 'ANO' then

                  TO_CHAR(oa.insercao_data,'RRRR')

              when 'MES' then

                  TO_CHAR(oa.insercao_data,'RRRR-MM')

              else

                  TO_CHAR(oa.insercao_data,'RRRR-MM-DD')

         End

having count(case artigo_activo_indc

                   when 1 then

                    1

                   else

                   null

                End

               )>1  

order by case 'MES' --p_tipo_agregacao

              when 'ANO' then

                  TO_CHAR(oa.insercao_data,'RRRR')

              when 'MES' then

                  TO_CHAR(oa.insercao_data,'RRRR-MM')

              else

                  TO_CHAR(oa.insercao_data,'RRRR-MM-DD')

         End  desc

Resultado:

Intervalo            nº artigos activos
2007-07                        3
 
Nota: 
O SQL foi desenvolvido de forma a apresentar as possibilidades de utilização da função CASE.

Reset de sequências

Há já algum tempo precisei de fazer um reset a uma sequência.
Não tendo encontrado nenhum método da Oracle que cumprisse o propósito, escrevi um procedimento.

Para quem tiver a mesma necessidade e ainda não possua um método melhor de o fazer, deixo aqui uma cópia do procedimento:

/**
 * Altera o valor de uma sequência
 *
 * @criacao 2006-05-15 Adriano Teixeira
 * @param pv_nome_seq      Nome da sequência a ser alterada
 * @param pi_valor_inicial Valor que desejamos colocar na sequência
**/

PROCEDURE seq_reset (
  pv_nome_seq        IN   VARCHAR2,
  pi_valor_inicial   IN   PLS_INTEGER
)
AS
  li_val      PLS_INTEGER;
  lv_inc_by   VARCHAR2 (15);
  li_increment_by PLS_INTEGER;
BEGIN
  -- Guarda o valor do INCREMENT BY
  EXECUTE IMMEDIATE 'SELECT increment_by FROM user_sequences WHERE sequence_name = ' 
                    || pv_nome_seq INTO li_increment_by;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pv_nome_seq || ' MINVALUE 0';
  
  EXECUTE IMMEDIATE 'SELECT ' || pv_nome_seq || '.NEXTVAL FROM dual'
               INTO li_val;
               
  -- Verificar qual o deslocamento necessário para se chegar ao valor desejado
  li_val := li_val - pi_valor_inicial + 1;
  
  -- Tratar a possibilidade de ser necessário colocar valores negativos
  IF li_val < 0
  THEN
     lv_inc_by := ' INCREMENT BY ';
     li_val := ABS (li_val);
  ELSE
     lv_inc_by := ' INCREMENT BY -';
  END IF;
  
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pv_nome_seq || lv_inc_by
                    || li_val;
  
  EXECUTE IMMEDIATE 'SELECT ' || pv_nome_seq || '.NEXTVAL FROM dual'
               INTO li_val;
               
  -- Colocar o Increment no valor desejado de novo
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pv_nome_seq || ' INCREMENT BY ' 
                    || TO_CHAR(li_increment_by);
END seq_reset;

Desmistificar comportamentos: NOT IN

Há uns dias, ocorreu, com um colega meu, uma situação aparentemente estranha.

Vamos ter como base as seguintes tabelas:

CREATE TABLE t1
(c11 NUMBER NOT NULL,
c12 NUMBER)
/
ALTER TABLE t1
ADD CONSTRAINT t1_pk PRIMARY KEY (c11)
/

CREATE TABLE t2
(c21 NUMBER NOT NULL,
c22 NUMBER)
/
ALTER TABLE t2
ADD CONSTRAINT t2_pk PRIMARY KEY (c21)
/

Com os seguintes dados:

INSERT INTO T1(C11, C12) VALUES(11, 1);
INSERT INTO T1(C11, C12) VALUES(12, 2);
INSERT INTO T1(C11, C12) VALUES(13, 3);
INSERT INTO T1(C11, C12) VALUES(15, 5);

INSERT INTO T2(C21, C22) VALUES(21, 1);
INSERT INTO T2(C21, C22) VALUES(22, 2);
INSERT INTO T2(C21, C22) VALUES(23, 3);
INSERT INTO T2(C21, C22) VALUES(24, 4);
INSERT INTO T2(C21, C22) VALUES(25, NULL);

A situação estranha passou-se com uma query que vou simplificar, na forma de:

SELECT *
FROM t1
WHERE c12 NOT IN (SELECT c22
FROM t2);

(Devemos ignorar a existência de melhores formas de atingir os objectivos desta query)

O problema é que a execução desta query resulta em.. zero resultados.
Este é, de facto, um resultado estranho; esperar-se-ia um registo: (15, 5).

Pelos vistos esta situação está documentada, pelo que, quem ler a documentação, esperará que, se na sub-query houver registos NULL, então o NOT IN retorne sempre zero registos.

Situação análoga, não aconteceria se a query fosse:

SELECT *
FROM t1
WHERE c12 IN (SELECT c22
FROM t2);

Nesta, os resultados obtidos coincidem com os esperados: ((11, 1), (12, 2), (13, 3)).

Tudo bem, mas o que interessa agora é: Porquê?

A suspeita aponta para a noção de NULL misturada com os conceitos associados à execução das operações IN e NOT IN.

Assim, temos os seguintes princípios:

- A comparação de qualquer elemento com NULL, nunca retorna TRUE em Oracle;
- Y IN (X1, X2) => Y = X1 OR Y = X2;
- Y NOT IN (X1, X2) => NOT (Y = X1 OR Y = X2) => Y <> X1 AND Y <> X2;
- X1 OR X2 = TRUE, X1 = TRUE, X2 = NULL
- X1 AND X2 = FALSE, X1 = TRUE, X2 = NULL

Ou seja, se aplicarmos os valores das querys aos princípios enumerados, temos que:
- Na versão NOT IN, basta que exista um NULL para que a condição em que ele ocorre se torne falsa, o que corresponde a um FALSE em toda a expressão (uma vez que FALSE é absorvente para o AND).
- Na versão IN, basta que exista uma condição que avalie TRUE, para que a expressão avalie TRUE também (já que TRUE é absorvente para o OR).

Assim, o resultado da query inicial

SELECT *
FROM t1
WHERE c12 NOT IN (SELECT c22
FROM t2);

(que é, portanto, zero resultados) deixa de ser estranho e passa a ser lógico.

Como começou...

Era uma vez...
Um conjunto de pessoas que trabalhavam com Oracle e que um dia acordaram que podia ser interessante registarem algum conhecimento de forma persistente: este blog tenta ser o local onde todos possam ser professores e alunos.
E espero que sejam felizes para sempre...