UPDATE с CASE и IN - Oracle

Ответ 1

Получено решение, которое выполняется. Не знаю, насколько это оптимально. Что я делаю, так это разделить строку в соответствии с http://blogs.oracle.com/aramamoo/2010/05/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement.html

Использование:
select regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level) from dual
connect by regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) is not null;

Итак, мой последний код выглядит так ($bp_gr1' - строки, такие как 1,2,3):

UPDATE TAB1
SET    BUDGPOST_GR1 =
          CASE
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( '$BP_GR1',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR1',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR1'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( ' $BP_GR2',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR2',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR2'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( ' $BP_GR3',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR3',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR3'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( '$BP_GR4',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR4',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR4'
             ELSE
                'SAKNAR BUDGETGRUPP'
          END;

Есть ли способ ускорить его выполнение?

Ответ 2

Вы сказали, что budgetpost является буквенно-цифровым. Это означает, что он ищет сравнения со строками. Вы должны попытаться включить ваши параметры в одинарные кавычки (и вам не хватает окончательного THEN в выражении Case).

UPDATE tab1   
SET budgpost_gr1=   CASE  
                        WHEN (budgpost in ('1001','1012','50055'))  THEN 'BP_GR_A'   
                        WHEN (budgpost in ('5','10','98','0'))  THEN 'BP_GR_B'  
                        WHEN (budgpost in ('11','876','7976','67465')) THEN 'What?'
                        ELSE 'Missing' 
                        END 

Ответ 3

Используйте to_number для преобразования budgpost в число:

when to_number(budgpost,99999) in (1001,1012,50055) THEN 'BP_GR_A' 

РЕДАКТИРОВАТЬ: Убедитесь, что в to_number достаточно 9, чтобы соответствовать самому большому сообщению бюджета.

Если есть нечисловые сообщения о бюджете, вы можете отфильтровать их с помощью предложения where в конце запроса:

where regexp_like(budgpost, '^-?[[:digit:],.]+$')

Ответ 4

"Список - это переменные/параметры, которые предварительно определены как списки, разделенные запятыми". Вы имеете в виду, что ваш запрос на самом деле

UPDATE tab1   SET budgpost_gr1=     
CASE  WHEN (budgpost in ('1001,1012,50055'))  THEN 'BP_GR_A'   
      WHEN (budgpost in ('5,10,98,0'))  THEN 'BP_GR_B'  
      WHEN (budgpost in ('11,876,7976,67465'))     
      ELSE 'Missing' END`

Если это так, вам нужна функция, чтобы взять строку и проанализировать ее в список чисел.

create type tab_num is table of number;

create or replace function f_str_to_nums (i_str in varchar2) return tab_num is
  v_tab_num tab_num := tab_num();
  v_start   number := 1;
  v_end     number;
  v_delim   VARCHAR2(1) := ',';
  v_cnt     number(1) := 1;
begin
  v_end := instr(i_str||v_delim,v_delim,1, v_start);
  WHILE v_end > 0 LOOP
    v_cnt := v_cnt + 1;
    v_tab_num.extend;
    v_tab_num(v_tab_num.count) := 
                  substr(i_str,v_start,v_end-v_start);
    v_start := v_end + 1;
    v_end := instr(i_str||v_delim,v_delim,v_start);
  END LOOP;
  RETURN v_tab_num;
end;
/

Затем вы можете использовать функцию следующим образом:

select column_id, 
   case when column_id in 
     (select column_value from table(f_str_to_nums('1,2,3,4'))) then 'red' 
   else 'blue' end
from  user_tab_columns
where table_name = 'EMP'

Ответ 5

Существует другое обходное решение, которое вы можете использовать для обновления с помощью соединения. В приведенном ниже примере предполагается, что вы хотите де-нормализовать таблицу, включив значение поиска (в этом случае сохраняя имя пользователя в таблице). Обновление включает в себя объединение, чтобы найти имя, и результат вычисляется в операторе CASE, который поддерживает имя, которое было найдено или не найдено. Ключом к выполнению этой работы является обеспечение того, чтобы все столбцы, выходящие из соединения, имели уникальные имена. В образце кода обратите внимание на то, как имя b.user_name конфликтует с столбцом a.user_name и должно быть псевдонимом с уникальным именем "user_user_name".

UPDATE
(
    SELECT a.user_id, a.user_name, b.user_name as user_user_name
    FROM some_table a
    LEFT OUTER JOIN user_table b ON a.user_id = b.user_id
    WHERE a.user_id IS NOT NULL
)
SET user_name = CASE
    WHEN user_user_name IS NOT NULL THEN user_user_name
    ELSE 'UNKNOWN'
    END;