if subquery returns no rows fetch all the records or ignore that condition-CodePudding

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#)

when the where clause sub query matches the filter data only that data should be displayed, when the where clause sub query returns no rows then that filter should not be applied for the where clause.

Also we have multiple queries so request you to a logic with the optimized solution.

CodePudding user response：

You can add a filter for when VISION_SUB = 'All' to short-cut the IN filter.

Additionally, if you expect the sbu, account_officer and vision_ouc matches to be in the same row then you should not be using multiple IN filters and should do it all in the same sub-query.

Something like:

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
                 )
       )

However, I'm not sure that the account_officers or vision_ouc columns exist in your outer h1 query so you need to fix that.

CodePudding user response：

you can use a case for this as

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#) is not null 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

But performance-wise, this is not a good idea.

If I were you, I am using a function/ SP using temporary tables.