In SQL "WITH AS" clause is not working. is there any alternative way?-CodePudding

I have one query, something like this:

    select col1,col2 from (
       with RESULTSET as (
       select * from t1 where rank_val=1 
      )  
      select  T1.col1, T1.col2    
      FROM RESULTSET T1, t88a, t88b
      where T1.col1=T88a.col1 and T88a.col2 = T1.col2
     AND T1.col2=T88b.col2 and T88b.col1 <> T1.col1
   ) where NOT (c1 IS NULL AND c2 IS NULL)   ORDER BY col1, col2;

I have a requirement where I need to use one outer With As, something like below:

WITH NEW AS(select col1,col2 from (
       with RESULTSET as (
       select * from t1 where rank_val=1 
      )  
      select  T1.col1, T1.col2    
      FROM RESULTSET T1, t88a, t88b
      where T1.col1=T88a.col1 and T88a.col2 = T1.col2
     AND T1.col2=T88b.col2 and T88b.col1 <> T1.col1
   ) where NOT (c1 IS NULL AND c2 IS NULL)   ORDER BY col1, col2)
   SELECT * FROM NEW;

Its giving me the exception:

 ORA-32034: unsupported use of WITH clause
 32034. 00000 -  "unsupported use of WITH clause"

How can I re write the query by removing the inner With As.

CodePudding user response：

Move all subqueries out, into a CTE. Something like this:

WITH
   resultset
   AS
      (SELECT *
         FROM t1
        WHERE rank_val = 1),
   temp
   AS
      (SELECT t1.col1, t1.col2
         FROM resultset t1
              JOIN t88a ON t88a.col1 = t1.col1
              JOIN t88b
                 ON     t88b.col2 = t1.col2
                    AND t88b.col1 <> t1.col1),
   new
   AS
      (SELECT col1, col2
         FROM temp
        WHERE NOT (    col1 IS NULL
                   AND col2 IS NULL))
  SELECT *
    FROM new
ORDER BY col1, col2;

CodePudding user response：

Your query can be rewritten as:

with RESULTSET as (
  select * from t1 where rank_val=1 
),
NEW AS(
  select col1,col2
  from (
    select  T1.col1, T1.col2    
    FROM    RESULTSET T1,
            t88a,
            t88b
    where   T1.col1=T88a.col1
    and     T88a.col2 = T1.col2
    AND     T1.col2=T88b.col2
    and     T88b.col1 <> T1.col1
  )
  where  NOT (col1 IS NULL AND col2 IS NULL)
  ORDER BY col1, col2
)
SELECT *
FROM   NEW;

However, you can simplify it down to:

SELECT T1.col1, T1.col2    
FROM   T1
       INNER JOIN t88a
       ON (   T1.col1=T88a.col1
          AND T88a.col2 = T1.col2 )
       INNER JOIN t88b
       ON (   T1.col2=T88b.col2
          AND T88b.col1 <> T1.col1 )
WHERE  t1.rank_val=1 
AND    (  t1.col1 IS NOT NULL
       OR t1.col2 IS NOT NULL )
ORDER BY col1, col2

But the NOT NULL checks in the WHERE clause are redundant as the JOIN conditions T1.col1=T88a.col1 and T88a.col2 = T1.col2 (etc.) will only ever be true when the values on both sides of the condition are NOT NULL so it can be simplified further to:

SELECT T1.col1, T1.col2    
FROM   T1
       INNER JOIN t88a
       ON (   T1.col1=T88a.col1
          AND T88a.col2 = T1.col2 )
       INNER JOIN t88b
       ON (   T1.col2=T88b.col2
          AND T88b.col1 <> T1.col1 )
WHERE  t1.rank_val=1 
ORDER BY col1, col2

CodePudding user response：

You can define WITH either as separate section or as a part of subquery. Sample:

WITH
    tbl_1 AS    -- CTE 1
        (
            Select 'A' "LTTR", 1 "NMBR" From Dual Union ALL
            Select 'B' "LTTR", 2 "NMBR" From Dual Union ALL
            Select 'C' "LTTR", 3 "NMBR" From Dual 
        ),
    tbl_2 AS    -- CTE 2
        (
            Select 'A' "LTTR", 10 "NMBR" From Dual Union ALL
            Select 'B' "LTTR", 20 "NMBR" From Dual Union ALL
            Select 'C' "LTTR", 30 "NMBR" From Dual 
        )
Select
    t1.LTTR "LTTR", (t1.NMBR * t2.NMBR) - x.NMBR "CALCULATION"
From
    tbl_1 t1
Inner Join
    tbl_2 t2 ON(t2.LTTR = t1.LTTR)
Inner Join
    (WITH
        tbl_x AS    -- CTE 3
        (
            Select 'A' "LTTR",  5 "NMBR" From Dual Union ALL
            Select 'B' "LTTR", 10 "NMBR" From Dual Union ALL
            Select 'C' "LTTR", 15 "NMBR" From Dual 
        )
        Select * From tbl_x
    ) x ON(x.LTTR = t1.LTTR)
Order By 
    t1.LTTR
    
/*  R e s u l t :
LTTR CALCULATION
---- -----------
A              5 
B             30 
C             75
*/

It is better to keep them all together - it's easier to manage them later.... Regards...