SQL oracle：解码和子查询选择结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6196141/
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: decode and subquery select result
提问by hanumant
I have a oracle query and part of it is calculating some value using DECODE. For example:
我有一个 oracle 查询，其中一部分是使用 DECODE 计算一些值。例如：
SELECT ...,
(SELECT DECODE((SELECT 23 FROM DUAL),
0, null,
(SELECT 23 FROM DUAL))
FROM DUAL)
FROM ...
Here the value "23" gets calculated at runtime, and it's quite complicated joins - multiple tables, uses PARTITION BYetc. So I want to avoid executing the same subquery if the value is not "0". Is there any way to write something like this
这里的值“23”是在运行时计算的，它是非常复杂的连接——多个表、用途PARTITION BY等。所以如果值不是“0”，我想避免执行相同的子查询。有没有办法写这样的东西
SELECT ...,
(SELECT DECODE ((SELECT 23 FROM DUAL) as test,
0, null,
test)
FROM DUAL)
FROM ...
回答by JosephStyons
Will this work for you? I've just moved the "23" to an inline table with a descriptive alias.
这对你有用吗？我刚刚将“23”移到了带有描述性别名的内联表中。
select ...,
(
select
decode (
computed_value.val,
0, null,
computed_value.val
)
from
(select 23 as val from dual) computed_value
)
from
...
A CASE statement might also add clarity, as in:
CASE 语句也可能会增加清晰度，例如：
select
...
,case when computed_value.val = 0
then null
else computed_value.val
end as my_field
from
(select 23 as val from dual) computed_value
...
回答by DCookie
Or:
或者：
WITH q AS (
SELECT 23 test, 16 test2 FROM dual
)
SELECT ...
, DECODE(q.test, 0, NULL, q.test) value
, CASE WHEN q.test2 = 0 THEN NULL
WHEN q.test2 = 16 THEN 1
ELSE q.test2
END another_value
FROM q, ...
Lets you use the query "q" throughout your main select, where ever a subquery is allowed. Called the WITH clause, or Common Table Expression, or Subquery Factoring. Read more about it at Oracle-Base.com.
允许您在整个主选择中使用查询“q”，只要允许子查询。称为 WITH 子句，或 Common Table Expression，或 Subquery Factoring。在Oracle-Base.com 上阅读有关它的更多信息。
回答by Cheran Shunmugavel
回答by janasainik
Better you would have use CASE statement. since the CASE statement is like a series of IF statements, only using the key word WHEN. A CASE statement is evaluated from top to bottom. If a condition is true, then corresponding THEN clause is executed and execution jumps to the END CASE (short circuit evaluation) clause.
最好使用 CASE 语句。因为 CASE 语句就像一系列 IF 语句，只使用关键字 WHEN。CASE 语句从上到下进行评估。如果条件为真，则执行相应的 THEN 子句并且执行跳转到 END CASE（短路评估）子句。
回答by Yusuf Soysal
You can use the subquery in from clause and do something like below:
您可以在 from 子句中使用子查询并执行如下操作：
select conf_key, decode(test, 0, null, test) from (
select conf_key, (select conf_value from config_values where conf_key = 'DOMAINID') as TEST from config_values )
