带in子句的nvl函数

Question

我的NVL函数有问题。我可以使用我的in子句propryl，但当我添加nvl时，它会出错。

我的查询是这样的：

SELECT  ci.date, 
SUM (cid.salary) amount, SUM (cid.slary_gross) gross_amount
FROM students ci,
orders cid           
WHERE 
ci.id IN  NVL((select id from sysadm.students
where status = 'IN' AND student_id= 24514 ORDER BY id
FETCH FIRST 3 ROWS ONLY), ci.id ) 

我的错误：01427. 00000 - "single-row subquery returns more than one row"

如果我删除了nvl函数，然后像下面这样写它正在运行。如何将nvl与in子句一起使用？

ci.id IN (select id from sysadm.students where status = 'IN' AND student_id= 24514 ORDER BY id FETCH FIRST 3 ROWS ONLY)

Answer 1

我怀疑子查询中的id是NULL。所以我建议：

with s3 as (
select s.id
from sysadm.students s.
where s.status = 'IN' and s.student_id = 24514
ORDER BY id
FETCH FIRST 3 ROWS ONLY
)
select . . .
where ci.id in (select id from s3) or
not exists (select 1 from s3);

这会检查您的id是否在列表中，或者列表是否为空。