Спецификация значения по условию

Функция

Выбор из множества значений одного значения по заданному условию.

Спецификация

   

Возможны два варианта:

< значимое выражение условия >::=
< список значений >::=
< спецификация значения по условию >::=

Синтаксические правила

  1. В первом случае вычисляется < значимое выражение условия >, стоящее сразу после CASE; если одно из его значений совпадает со < значимым выражением условия >, указанным в первом WHEN, то возвращается результат < значимого выражения > из первого THEN и т.д. Если же оно не совпадает ни с одним из значений, указанных в WHEN, то возвращается результат выражения, стоящего после ELSE, либо NULL-значение, если ELSE отсутствует.

  2. При сравнении NULL-значения < значимого выражения условия > в опции CASE с NULL-значением < значимого выражения условия > в опции WHEN:

    • TRUE, если ядро СУБД запущено без ключа /COMPATIBILITY=STANDARD;

    • FALSE, если ядро СУБД запущено с ключом /COMPATIBILITY=STANDARD.

  3. Во втором случае сначала проверяется первое условие; если оно выполняется, то возвращается результат, указанный в первом THEN и т.д.

  4. Все возвращаемые выражения должны иметь одинаковый тип (символьные могут быть разной длины, тип результата берется по большей длине).

  5. Допустимыми < предикатами > являются:

    • < предикат сравнения >

      SELECT make, CASE WHEN year=70 THEN 1970
      ELSE 1971 END FROM Auto;
      
      select distinct make, model from auto 
       where make=case cylnders when 8 then upper('ford')
                                else upper('chrysler') end;
      
      |CHRYSLER |DODGE CHALLENGER SIX |
      |FORD     |LINCOLN CONTINENTAL  |
      |FORD     |LTD COUNTRY SQUIRE   |
      |FORD     |MERCURY COMET GT V8  |
      |FORD     |MERCURY MONTEREY     |
      |FORD     |MUSTANG BOSS 351     |

      Две одинаковые по результатам конструкции:

      SELECT
        CASE Make
          WHEN 'FORD' THEN 1
          WHEN 'FERRARI' THEN 2
          ELSE 3
        END
        FROM Auto;
      
      SELECT
        CASE
          WHEN Make='FORD' THEN 1
          WHEN Make='FERRARI' THEN 2
          ELSE 3
        END
        FROM Auto;
    • < интервальный предикат >

      SELECT make, CASE WHEN year between 60 and 70 THEN 1970 ELSE 1971 END FROM Auto;
    • < предикат вхождения >

      SELECT make, CASE WHEN year in (60, 70) THEN 1970 ELSE 1971 END FROM Auto;
      select * from test where case when id in (select id from test where rowid >1) then 1 else 0 end = 1;
      select * from test where case when (id in (select id from test where rowid=1) or id in (select id from test where rowid=3)) then 1 else 0 end = 1;
    • < предикат подобия >

      SELECT make, CASE WHEN Make like 'FO%' THEN 1 WHEN Make='GE%' THEN 2 ELSE 3 END FROM Auto;
    • < предикат неопределенного значения >

      SELECT CASE C WHEN NULL THEN
        'NULL'
        ELSE
          'NOT-NULL'
        END FROM T;

Общие правила

  1. Если в CASE-выражении выбираются альтернативы, имеющие различные типы данных (CHAR/VARCHAR, NCHAR/NCHAR VARYING), то результату присваивается тип данных переменной длины (VARCHAR, NCHAR VARYING).

  2. В качестве < значимого выражения > не могут использоваться BLOB и EXTFILE столбцы. Эти конструкции некорректны:

    case ... when .. then NULL else BLOB_COLUMN end
    case ... when .. then BLOB_COLUMN else NULL end
  3. Если числовые < значимые выражения > в конструкции CASE WHEN < предикат > имеют различный тип данных, то выполняется автоматическое преобразование их к типу данных основного < значимого выражения > конструкции CASE:

    • в качестве типа данных основного < значимого выражения > выбирается тип данных первого неконстантного выражения (например, столбца);

      create or replace table int2(i2 smallint, d2 real);
      insert into int2 values(1,1.1);
      insert into int2 values(2,2.2);
      insert into int2 values(3,3.3);
      !Тип данных результата  – короткое целое:
      select CASE WHEN (i2 <  3) THEN 2.2 ELSE i2 END from int2;
      |          2|
      |          2|
      |          3|
    • если все выражения константные, выбирается тип первого выражения по порядку следования;

      ! Тип данных результата  – целое число:
      select CASE WHEN (i2 <  3) THEN 0 ELSE 1.1 END from int2;
      |          0|
      |          0|
      |          1|
      ! Тип данных результата  – число с фиксированной точкой:
      select CASE WHEN (i2 <  3) THEN 0.1 ELSE 1 END from int2;
      |                    0.1         |
      |                    0.1         |
      |                    1.0         |
    • если в CASE-конструкции имеется несколько неконстантных выражений разных числовых типов, будет выдаваться код завершения 2031 о несовместимости типов данных (в этом случае необходимо использовать явное преобразование типов с помощью конструкции CAST);

      ! Выдается код завершения  2031 – несовместимые  типы данных
      select CASE WHEN (i2 <  3) THEN d2 ELSE i2 END from int2;
    • если неконстантные выражения отсутствуют или имеется только одно из них, типы данных всех выражений, отличающиеся от основного типа данных, будут приведены к основному с помощью неявной конструкции CAST.

      select CASE WHEN (i2 <  3) THEN i2  ELSE 0 END from int2;
             |     1|
             |     2|
             |     0|
      select CASE WHEN (i2 <  3) THEN 0 ELSE i2 END from int2;
             |     0|
             |     0|
             |     3|
  4. В общем случае все предикаты обрабатывают только одну запись (за исключением предикатов сравнения). В конструкции CASE для предиката EXISTS/NOT EXISTS разрешается использовать выборку подзапроса из нескольких записей.

    SELECT CASE WHEN (EXISTS (SELECT MAKE FROM AUTO)) THEN TRUE END FROM (SELECT 1);

Пример

create or replace table test(n int, ch char(10));
insert into test values (0,'val0');
insert into test values (1,'val1');
insert into test values (2,'val2');
insert into test values (3,'val3');
insert into test values (4,'val4');
insert into test values (5,'val5');
insert into test values (NULL,NULL);
select n,
       case n
         when 1, 0.0, 3e0 then 'defined {0|1|3}'
         when 5.0 then 'defined 5'
         when 2e0,4 then 'defined {2|4}'
         when NULL then 'defined NULL'
         else 'undefined'
       end status1,
       case ch
         when 'val1', 'val' || '0' then 'defined {val0|val1}'
         when 'val' || '5', 'val3', 'val4' then 'defined {val3|val4|val5}'
         when NULL then 'defined NULL'
         when 'val2' then 'defined val2'
         else 'undefined'
       end status2,
       case (n, ch)
         when (1,'val1'), (2.0,'val' || '2'), (3e0,'val3') then 'defined {1|2|3}'
         when (5e0, 'val' || '5') then 'defined 5'
         when (0e0, 'val0'),(4,'val4')  then 'defined {0|4}'
         when (NULL,NULL) then 'defined NULL'
         else 'undefined'
       end status3
  from test;
drop table test;

Результат работы select-запроса примера:

 N       STATUS1         STATUS2                  STATUS3
 -       -------         -------                  -------
|   0|defined {0|1|3}|defined {val0|val1}     |defined {0|4}  |
|   1|defined {0|1|3}|defined {val0|val1}     |defined {1|2|3}|
|   2|defined {2|4}  |defined val2            |defined {1|2|3}|
|   3|defined {0|1|3}|defined {val3|val4|val5}|defined {1|2|3}|
|   4|defined {2|4}  |defined {val3|val4|val5}|defined {0|4}  |
|   5|defined 5      |defined {val3|val4|val5}|defined 5      |
|    |defined NULL   |defined NULL            |defined NULL   |