如果子查询不返回任何行，则获取所有记录或忽略该条件

Question

SELECT DISTINCT VISION_SBU, SBU_DESCRIPTION
  FROM (SELECT 'All' VISION_SBU, 'All' SBU_DESCRIPTION, 1 sort_order FROM DUAL
        UNION ALL
        SELECT ALPHA_SUB_TAB, ALPHA_SUBTAB_DESCRIPTION, case when ALPHA_SUB_TAB = 'NA' then 99999 else row_number() over (order by ALPHA_SUB_TAB) +1 end sort_order
          FROM ALPHA_SUB_TAB
         WHERE ALPHA_TAB = 3) H1
where VISION_SBU in (
select t2.sbu from vision_user t1 left outer join VU_RESTRICT_SBU t2
on t1.vision_id = t2.vision_id
where t1.vision_id = #VISION_ID#)
and  account_officers in  (
select t2.account_officer from vision_user t1 left outer join VU_RESTRICT_SBU t2
on t1.vision_id = t2.vision_id
where t1.vision_id = #VISION_ID#)
and  vision_ouc in  (
select t2.vision_ouc from vision_user t1 left outer join VU_RESTRICT_SBU t2
on t1.vision_id = t2.vision_id
where t1.vision_id = #VISION_ID#)

当where子句子查询只匹配筛选器数据时，应该显示数据，当where子句子查询不返回任何行时，则不应对where子句应用该筛选器。

此外，我们有多个查询，因此请您使用优化的解决方案进行逻辑处理。

Answer 1

您可以为何时VISION_SUB = 'All'添加一个筛选器，以缩短IN筛选器.

此外，如果您期望sbu、account_officer和vision_ouc匹配在同一行，那么就不应该使用多个IN筛选器，而应该在同一个子查询中完成所有这些操作。

类似于：

SELECT DISTINCT
       VISION_SBU,
       SBU_DESCRIPTION
FROM   ( SELECT 'All' VISION_SBU,
                'All' SBU_DESCRIPTION,
                1 sort_order
         FROM   DUAL
       UNION ALL
         SELECT ALPHA_SUB_TAB,
                ALPHA_SUBTAB_DESCRIPTION,
                case when ALPHA_SUB_TAB = 'NA' then 99999 else row_number() over (order by ALPHA_SUB_TAB) +1 end sort_order
         FROM   ALPHA_SUB_TAB
         WHERE  ALPHA_TAB = 3
       ) H1
WHERE  (  VISION_SBU = 'All'
       OR EXISTS ( select 1
                   from   vision_user t1
                          left outer join VU_RESTRICT_SBU t2
                          on t1.vision_id = t2.vision_id
                   where t1.vision_id        = #VISION_ID#
                   AND   h1.VISION_SBU       = t2.sbu
                   AND   h1.account_officers = t2.account_officer
                   AND   h1.vision_ouc       = t2.vision_ouc
                 )
       )

但是，我不确定account_officers或vision_ouc列是否存在于外部h1查询中，因此需要对其进行修复。

Answer 2

您可以将这个案例用作

case when  (select count(t2.sbu) from vision_user t1 left outer join VU_RESTRICT_SBU t2
on t1.vision_id = t2.vision_id
where t1.vision_id = #VISION_ID#) > 0 then 
VISION_SBU in (
select t2.sbu from vision_user t1 left outer join VU_RESTRICT_SBU t2
on t1.vision_id = t2.vision_id
where t1.vision_id = #VISION_ID#) else 1 = 1 end

但就性能而言，这不是个好主意。

如果我是您，我将使用使用临时表的函数/ SP。