用CASE和IN - Oracle更新

I wrote a query that works like a charm in SQL Server. Unfortunately it needs to be run on an Oracle db. I have been searching the web inside out for a solution on how to convert it, without any success :/

我编写了一个查询，它在SQL Server中非常有用。不幸的是，它需要在Oracle db上运行。我一直在彻底地搜索网页，寻找如何转换它的解决方案，但没有成功:/

The query looks like this i SQL:

查询如下:

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`

My problem is also that the columns budgetpost_gr1 and budgetpost is alphanumeric and Oracle seems to want to see the list as numbers. The list are variables/parameters that is pre-defined as comma separated lists, which is just dumped into the query.

我的问题还在于budgetpost_gr1和budgetpost是字母数字，而Oracle似乎希望将列表视为数字。列表是预先定义为逗号分隔的列表的变量/参数，它将被转储到查询中。

5 个解决方案

#1

You said that budgetpost is alphanumeric. That means it is looking for comparisons against strings. You should try enclosing your parameters in single quotes (and you are missing the final THEN in the Case expression).

你说budgetpost是字母数字。这意味着它正在寻找与字符串的比较。您应该尝试将参数括在单引号中(而您在Case表达式中丢失了final)。

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 

#2

"The list are variables/paramaters that is pre-defined as comma separated lists". Do you mean that your query is actually

“列表是预先定义为逗号分隔列表的变量/参数”。您的意思是您的查询实际上是吗

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`

If so, you need a function to take a string and parse it into a list of numbers.

如果是，您需要一个函数来获取字符串并将其解析为数字列表。

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;
/

Then you can use the function like so:

然后你可以使用这样的函数:

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'

#3

Use to_number to convert budgpost to a number:

使用to_number将budgpost转换为数字:

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

EDIT: Make sure there are enough 9's in to_number to match to largest budget post.

编辑:确保有足够的9在to_number匹配最大的预算职位。

If there are non-numeric budget posts, you could filter them out with a where clause at then end of the query:

如果有非数字的预算文章，您可以在查询末尾的where子句中过滤它们:

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

#4

Got a solution that runs. Don't know if it is optimal though. What I do is to split the string according to http://blogs.oracle.com/aramamoo/2010/05/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement.html

得到一个运行的解决方案。但不知道这是否是最佳选择。我要做的是根据http://blogs.oracle.com/aramoo/20105/how_split_comma_apart—string_and_pass_in_clause_of_select_statement.html来分割字符串

Using:
select regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level) from dual
connect by regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) is not null;

的使用:选择regexp_substr(“1、2、3”,“[^,]+”,1级)的双重连接的regexp_substr(“1、2、3”,“[^,]+”,1级)不是零;

So my final code looks like this ($bp_gr1' are strings like 1,2,3):

所以我的最终代码是这样的($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;

Is there a way to make it run faster?

有没有办法让它跑得更快?

#5

There is another workaround you can use to update using a join. This example below assumes you want to de-normalize a table by including a lookup value (in this case storing a users name in the table). The update includes a join to find the name and the output is evaluated in a CASE statement that supports the name being found or not found. The key to making this work is ensuring all the columns coming out of the join have unique names. In the sample code, notice how b.user_name conflicts with the a.user_name column and must be aliased with the unique name "user_user_name".

您还可以使用另一个变通方法来使用连接进行更新。下面的示例假设您希望通过包含一个查找值(在此情况下将用户名存储在表中)来对表进行反规范化。更新包含一个连接来查找名称，输出在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;