oracle NVL 中的 Select 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16695978/
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
Select statement inside NVL
提问by Naama Zrihen
I'm trying to run the following query:
我正在尝试运行以下查询：
select a.*,
case when NVL (SELECT max(b.field1)
FROM b
where b.field2 = a.tbl_a_PK , 'TRUE') = 'TRUE'
then 'has no data in b'
else 'has data in b' end as b_status
from a
I checked and the select inside the nvl returns only 1 value (so there shouldn't be a problem there). However I'm getting 'ORA-00936: missing expression'
我检查过并且 nvl 中的选择只返回 1 个值（所以那里应该没有问题）。但是我收到“ORA-00936：缺少表达”
回答by ThinkJet
NVL()requires 2 parameters: expression to test and default value e.g. nvl(some_field, 111). You just need to isolate query parameter by braces and provide second parameter like in this statement:
NVL()需要 2 个参数：要测试的表达式和默认值，例如nvl(some_field, 111). 您只需要通过大括号隔离查询参数并提供第二个参数，如以下语句：
select nvl( (select 1 from dual), 34) from dual
In your variant parser expects comma after SELECTkeyword and can't parse remaining string.
在您的变体解析器中，SELECT关键字后需要逗号并且无法解析剩余的字符串。
Exactly your statement must look like this:
您的语句必须如下所示：
select
a.*,
case when NVL(
( SELECT max(b.field1)
FROM b
where b.field2 = a.tbl_a_PK
),
'TRUE'
) = 'TRUE'
then 'has no data in b'
else 'has data in b' end as b_status
from a
Hope this helps ...
希望这可以帮助 ...
UpdateIn terms of performance is better to use existsrather then max:
更新在性能方面最好使用exists而不是max：
select
a.*,
case when exists
( SELECT null
FROM b
where b.field2 = a.tbl_a_PK
and
b.field2 is not null
and
rownum = 1
),
then 'has data in b'
else 'has no data in b' end as b_status
from a
回答by vc 74
If you're searching for records in a which have/don't have associated records in b
如果您在 a 中搜索在 b 中有/没有关联记录的记录
select a.*,
case when b.field2 is null then 'has no data in b'
else 'has data in b'
as b_status
from a left outer join b
on a.tbl_a_PK = b.field2;
Should do it
应该做
回答by EProgrammerNotFound
the NVL(string1, replace_with) function requires 2 parameters, see docs here:
http://www.techonthenet.com/oracle/functions/nvl.php
Ora 10g docs: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
Since you know the problem, this query can fix it:
NVL(string1, replace_with) 函数需要 2 个参数，请参阅此处的
文档：http:
//www.techonthenet.com/oracle/functions/nvl.php
Ora 10g 文档：http: //docs.oracle.com/cd/B19306_01 /server.102/b14200/functions105.htm
既然你知道这个问题，这个查询可以解决它：
select a.*,
case
when (SELECT NVL(b.field2, 0) FROM b where b.field2 = a.tbl_a_PK and rownum = 1) > 0 then
'has data in b'
else
'has no data in b'
end b_status
from a
and runs faster.
You don't need max() to check if the value exists in another table, simply check if the primary key is not null.
并且跑得更快。
您不需要 max() 来检查该值是否存在于另一个表中，只需检查主键是否为空。
