How to use WITH clause with UNION ALL in oracle SQL-CodePudding

GOOD Day for you guys , i have a question regarding the with clause with union all the select statment in the procudre is working fine but when i'm trying to add condiotion to each with clause it giving this error ("Inappropriate into") the below is an example of what i'm trying to do :


    

-------------------------------------------SOO1

            WITH SS AS ((        SELECT  /) */
                        COUNT(ROWNUM) AS S001
                     FROM x.MSSE A
                    WHERE (SUCCESS_STATUS='F'
   

                        UNION ALL

                    SELECT
                          COUNT(ROWNUM) AS S001
                     FROM X.MSSE A
                    WHERE (SUCCESS_STATUS='N'
                    
                
       ),

S333 AS
(SELECT             /*  INDEX (A MSSEADTD_SI02) */
                     COUNT(ROWNUM) AS S333_
                   FROM X.MSSE A
                    WHERE (SUCCESS_STATUS='M' )
                

                    union all

                    SELECT       /*  INDEX (A MSSEORMD_SI03) */
                          COUNT(ROWNUM) AS S333_
                     FROM HHL7.MSSEORMD A
                    WHERE (SUCCESS_STATUS='A'
                    ),

LIS AS
 (SELECT  /*  INDEX (A MSSEORUD_SI02) */
     COUNT(ROWNUM)AS LIS_
      FROM A.MSS A
       WHERE (SUCCESS_STATUS='D')

SELECT SUM(S001) INTO Value_A
FROM
(
    SELECT S001 FROM SS
)        

UNION ALL

SELECT SUM(S333_) INTO VALUE_C  
FROM
(SELECT S333_ FROM S333)  
 
UNION ALL  

SELECT SUM(LIS_)INTO VALUE_D FROM
(SELECT LIS_ FROM LIS)



IF Value_A > 30000 THEN  
--DO THIS 
 
IF VALUE_C > 30000 THEN
--DO THAT 
--...ETC

CodePudding user response：

Your query is equivalent to just:

  SELECT set_a.S001, set_c.S333_, set_d.LIS_
    INTO Value_A, VALUE_C, VALUE_D
    FROM (SELECT COUNT(1) AS S001
            FROM x.MSSE
           WHERE SUCCESS_STATUS IN ('N','F')) set_a
       , (SELECT COUNT(1) AS S333_ 
            FROM X.MSSE
           WHERE SUCCESS_STATUS IN ('M','A')) set_c
       , (SELECT COUNT(1) AS LIS_
            FROM A.MSS A
           WHERE SUCCESS_STATUS = 'D') set_d;
            
  IF Value_A > 30000 THEN  
  --DO THIS 
  END IF;
  IF VALUE_C > 30000 THEN
  --DO THAT
  END IF;

There is 0 need of those unions

CodePudding user response：

The way I see it, it is UNION - but somewhat different from the one you wrote because part of data comes from one table (x.msse) and another from hh17.msseormd. It is unclear whether you can (or can not) join these two tables so ... union can be one option.

As number and datatype of columns returned by select statements in union must match, the 2nd select selects two 0 constant values.

The final select sums values returned in the CTE.

with temp as
   (-- values from X.MSSE table
    select 
      sum(case when success_status in ('F', 'N') then 1 else 0 end) s001,
      sum(case when success_status in ('M')      then 1 else 0 end) s333,
      sum(case when success_status in ('D')      then 1 else 0 end) lis
    from x.msse
    union all
    -- values from HH17.MSSEORMD table
    select 
      0 s001,
      sum(case when success_status in ('A')      then 1 else 0 end)  s333,
      0 lis
    from hhl7.msseormd
   )
select sum(s001), sum(s333), sum(lis)
  into value_a, value_c, value_d
from temp;

IFs follow, but - that's easier part, once you fetch value_a etc.