oracle：可以跨表使用 DECODE 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4917202/
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
oracle: can you use DECODE across tables?
提问by Sinaesthetic
pretty new to the function, but as i understand DECODE tests the specified attribute (arg1) against another specified value (arg2) and if they match, it displays a result (arg3) otherwise if it doesn't match, the default will display (arg4)
该函数非常新，但据我了解，DECODE 会根据另一个指定值 (arg2) 测试指定属性 (arg1)，如果它们匹配，则显示结果 (arg3)，否则，如果不匹配，则默认将显示 ( arg4)
DECODE(firstname,'John','last is doe','I don't know the last name')
Can you do this using data from another table to emulate a join? like
您可以使用另一个表中的数据来模拟连接吗？喜欢
DECODE(publisherid, publisher.pubid, publisher.name,'unknown')
I'm trying it but it isn't working. didn't know if it was possible or maybe im just doing it wrong. Any thoughts?
我正在尝试，但它不起作用。不知道这是否可能，或者我只是做错了。有什么想法吗？
回答by OMG Ponies
The 11g documentationstates that the search parameters can be an expression, so what you have should work. If you get ORA errors, you should let us know. Otherwise, it's likely you don't have data to match, or the publisher.name column is null...
将11g通用文档状态，搜索参数可以是一个表达式，所以你有什么应该工作。如果您收到 ORA 错误，您应该告诉我们。否则，您可能没有要匹配的数据，或者publisher.name 列为空...
You wouldn't use it to emulate a join -- you still want to actually JOIN (ANSI 89 or 92 syntax, but preferably 92) the tables appropriately or you'll be dealing with a lot of garbage rows from a cartesian product which the DECODE will then execute upon.
您不会使用它来模拟连接——您仍然希望实际连接（ANSI 89 或 92 语法，但最好是 92）表，否则您将处理来自笛卡尔积的大量垃圾行DECODE 然后将执行。
Unless you're on pre-9i (8.1.6, according to Ask Tom), I wouldn't waste your time with DECODE and use the ANSI CASE statement instead:
除非您使用的是 9i 之前的版本（根据 Ask Tom 的说法，为 8.1.6），否则我不会将您的时间浪费在 DECODE 上，而是使用 ANSI CASE 语句：
CASE
WHEN publisherid = publisher.pubid THEN publisher.name
ELSE 'unknown'
END
回答by Gary Myers
You can use it between columns of different tables like so:
您可以在不同表的列之间使用它，如下所示：
select oi.order_id, oi.product_id, oi.unit_price, p.list_price,
decode(oi.unit_price, p.list_price, 'No Discount',
p.list_price - oi.unit_price||' discount') disc
from demo_ord_items oi
join demo_product_info p on p.product_id = oi.product_id
where oi.order_id = 1
In theory you can use it as a join condition
理论上你可以用它作为连接条件
select oi.order_id, oi.product_id, oi.unit_price, p.list_price
from demo_ord_items oi
join demo_product_info p on p.product_id = oi.product_id
and decode(oi.unit_price, p.list_price,0,1) = 1
where oi.order_id = 1
but it isn't common. A condition is boolean (true/false) and a decode returns a number or character, so the decode is sort of redundant (or can probably be re-written to be clearer).
但这并不常见。条件是布尔值（真/假）并且解码返回一个数字或字符，因此解码有点多余（或者可能可以重写以使其更清晰）。
回答by Ronnis
Use DECODEwhen any of the following is true:
使用DECODE时，以下任一为真：
- You want to write non-portable SQL
- You want to to write code that only Oracle developers recognize and understand
- You want to write code that is hard to read
- You want to increase the chance of introducing bugs the next time you change the code
- You want the optimizer to generate suboptimal execution plans (when used in where-clause).
- You are writing throw-away code
- 您想编写不可移植的 SQL
- 您想编写只有 Oracle 开发人员才能识别和理解的代码
- 您想编写难以阅读的代码
- 您希望在下次更改代码时增加引入错误的机会
- 您希望优化器生成次优执行计划（在 where 子句中使用时）。
- 您正在编写一次性代码
In short, don't use DECODE at all. There is nothing it can't do that couldn't be done with a CASE/WHEN. Yes, there will be more typing at the keyboard, and it is "worth" it.
简而言之，根本不要使用 DECODE。没有什么是它不能做的，不能用CASE/WHEN. 是的，在键盘上打字会更多，这是“值得”的。
Having said that, there is one throw-away case when I always prefer DECODE, that has to do with how it deals with NULL. The scenario is when I have two (hopefully) identical tables with the same key, and when I want to find out if there are any rows where the two tables don't agree on a column value.
话虽如此，我总是喜欢一种扔掉的情况DECODE，这与它如何处理有关NULL。这种情况是当我有两个（希望）相同的表具有相同的键时，并且当我想找出是否有任何行的两个表在列值上不一致时。
For example, let's say you have rewritten a batch procedure for performance reasons, and you want to make sure that the new version produces the same results as the old version. In this situation, you would consider NULLand NULLto be the same "value". DECODEhappens to behave just like that.
例如，假设您出于性能原因重写了一个批处理过程，并且您希望确保新版本产生与旧版本相同的结果。在这种情况下，你会考虑NULL和NULL成为相同的“价值”。DECODE碰巧就是那样。
decode(a, b, 'Y', 'N')
would translate to the following case/when construct
将转换为以下情况/何时构造
case when a = b then 'Y'
when a is null and b is null then 'Y'
else 'N'
end
Here is an example of what I mean
这是我的意思的一个例子
with old_table as(
select 1 as id, 'A' as old_col from dual union all
select 2 as id, 'A' as old_col from dual union all
select 3 as id, 'A' as old_col from dual union all
select 4 as id, null as old_col from dual
)
,new_table as(
select 1 as id, 'A' as new_col from dual union all
select 2 as id, 'B' as new_col from dual union all
select 3 as id, null as new_col from dual union all
select 4 as id, null as new_col from dual
)
select id
,old_col
,new_col
,decode(old_col, new_col, 'Y', 'N') as has_same_val
from old_table
join new_table using(id);
ID OLD_COL NEW_COL HAS_SAME_VAL
-- ------- ------- ------------
1 A A Y
2 A B N
3 A null N <-- Note those
4 null null Y <-- two cases
So to find the difference I would do:
所以要找到差异，我会这样做：
where decode(old_col1, new_col1, 'same', 'diff') = 'diff'
or decode(old_col2, new_col2, 'same', 'diff') = 'diff'
or decode(old_col3, new_col3, 'same', 'diff') = 'diff'
or decode(old_col4, new_col4, 'same', 'diff') = 'diff'
or decode(old_col5, new_col5, 'same', 'diff') = 'diff'
回答by dsmoljanovic
As far as syntax is concerned, you got it correct. If you always get 'unknown' as result output your publisherid and check if you have it in the table.
就语法而言，你说得对。如果你总是得到“未知”作为结果输出你的publisherid并检查你是否在表中。
If ID-s are text fields, note that the decode is case sensitive. Also check if perhaps you have trimmed ids somewhere.
如果 ID-s 是文本字段，请注意解码区分大小写。还要检查您是否在某处修剪了 id。
