案例陈述不执行第二案件

I need your assistance with the below query. Could you please assist?

我需要您的帮助以下查询。你能帮忙吗？

Below are sample records from the Oracle database:

以下是Oracle数据库中的示例记录：

id:

ID：

L7985W6W7W5HJYR5 
LJHGTIKFF89EE7HJJU7 
LIUH789KEHD7W9JHF
LYUEJDJ8F2NIBRANCH
LOI9DDH7E38BRANCH
L8908HYS6WIBRANCH
PJHS79209KJSJYIEJHSV12

My intention is if b.branch_id begins with L then it will remove leading zeroes and strip characters but if b.branch_id ends with ‘%BRANCH%' then I would like it to merge with another field but is not working. The first part is working, if it begins with L then it works but somehow never executes the ‘%BRANCH%' part.

我的意图是，如果b.branch_id以L开头，那么它将删除前导零和剥离字符，但如果b.branch_id以'％BRANCH％'结尾，那么我希望它与另一个字段合并但不起作用。第一部分是工作，如果它从L开始然后它工作但不知何故从不执行'％BRANCH％'部分。

Select distinct b.branch_id,
b.branch_date

CASE 
 WHEN b.branch_id like L%'
  THEN TRIM(substr((TO_CHAR(TRIM(LEADING 0 FROM b.branch_id))),-10))
   WHEN b.branch_id like ‘%BRANCH%'
     THEN substr(b.branch_loc,2,9) || substr(i.branch_name,1,9)
       END AS BRANCH_INFO

from tbl_brach b
JOIN tbl_branch_info i
on b.branch_id = i.branch_id_key 

where b.branch_id like L%'

1 个解决方案

#1

From the documentation:

从文档：

In a simple CASE expression, Oracle Database searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr.
...
For a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr.

在一个简单的CASE表达式中，Oracle数据库搜索第一个WHEN ... THEN对，其expr等于comparison_expr并返回return_expr。 ...对于简单的CASE表达式，数据库仅在将每个comparison_expr值与expr进行比较之前对其进行评估，而不是在将任何compare_expr值与expr进行比较之前对其进行评估。

You need to arrange the case statement so the most restrictive rule is matched first, otherwise it is never reached:

您需要安排case语句，以便首先匹配最严格的规则，否则永远不会达到：

CASE
  WHEN b.branch_id like ‘L%BRANCH%'
    THEN substr(b.branch_loc,2,9) || substr(i.branch_name,1,9)
  WHEN b.branch_id like L%'
    THEN TRIM(substr((TO_CHAR(TRIM(LEADING '0' FROM b.branch_id))),-10))
END AS BRANCH_INFO

You could also say

你也可以说

  WHEN b.branch_id LIKE 'L%' AND b.branch_id LIKE '%BRANCH%' THEN

but it's simpler to combine into a single LIKE pattern.

但是组合成单个LIKE模式更简单。

Your query also has a where clause that makes the L% in the case expression redundant, but I'm not sure if that is temporary. If it is then you may want an ELSE to handle all values that don't start with L, unless you want them to (implicitly) be null.

您的查询还有一个where子句，它使case表达式中的L％变为冗余，但我不确定这是否是临时的。如果是，那么您可能希望ELSE处理所有不以L开头的值，除非您希望它们（隐式地）为null。

As Laurel noted, if you want the value to end with 'BRANCH' then you don't want the final wildcard % sign, as that will also match if there are more characters after BRANCH. You don't have that in your sample data.

正如Laurel所指出的，如果你希望值以'BRANCH'结尾，那么你不需要最后的通配符％符号，因为如果在BRANCH之后有更多的字符，它也会匹配。您的样本数据中没有。

#1