在 Oracle 中的 CASE/THEN 中使用 LIKE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9376290/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
use LIKE inside CASE/THEN in Oracle?
提问by Data-Base
I have this part of the code
我有这部分代码
WHERE p.id = m.id AND (
CASE
WHEN (c.match_text IS NOT NULL)
THEN (
(lower(p.station) LIKE '%'||c.match_text||'%')
OR
(
(lower(p.station) LIKE '%sealed in%')
AND
(lower(p.affected_units) LIKE '%'||c.match_text||'%')
)
)
WHEN(u.name LIKE '%Memory%')
THEN (
(lower(p.affected_fuels) LIKE '%nuclear%')
AND
I get error with LIKE in this (lower(p.station) LIKE '%'||c.match_text||'%')!!
我在这方面遇到 LIKE 错误（lower(p.station) LIKE '%'||c.match_text||'%'）！！
why is that?
这是为什么？
I was thinking this approach will fix the issue:
我认为这种方法可以解决这个问题：
WHEN (c.match_text IS NOT NULL)
THEN (
CASE WHEN
(lower(p.station) LIKE '%'||c.match_text||'%')
OR
(
(lower(p.station) LIKE '%sealed in%')
AND
(lower(p.affected_units) LIKE '%'||c.match_text||'%')
)
THEN true
ELSE false
END
)
Thanks
谢谢
回答by APC
CASE() is basically a translator: it returns a value for a given set of conditions. The problem you have is that you are trying to force it to return a BOOLEAN value. This won't work because BOOLEAN is not a valid SQL data type.
CASE() 基本上是一个翻译器：它为给定的一组条件返回一个值。您遇到的问题是您试图强制它返回 BOOLEAN 值。这将不起作用，因为 BOOLEAN 不是有效的 SQL 数据类型。
So I think you'll have to implement your logic as nested AND/OR clauses. It's a bit gnarly but not much more complicated than your original statement. Something like this:
所以我认为你必须将你的逻辑实现为嵌套的 AND/OR 子句。这有点粗糙，但并不比你原来的陈述复杂多少。像这样的东西：
WHERE p.id = m.id
AND (
( c.match_text IS NOT NULL
AND ( lower(p.station) LIKE '%'||c.match_text||'%'
OR ( lower(p.station) LIKE '%sealed in%'
AND lower(p.affected_units) LIKE '%'||c.match_text||'%'
)
)
)
OR (u.name LIKE '%Memory%'
AND lower(p.affected_fuels) LIKE '%nuclear%'
AND ...
