4 Replies Latest reply: Mar 17, 2011 5:34 AM by BluShadow RSS

    how to create a view  with "WITH CLAUSE"

    KPR
      Hi,

      I have a query with "WITH" CLAUSE , I need to create a view on this query. But I am getting error like

      ORA-32034 : Unsupported sue of WITH clause.

      Please help me...!!

      Please find below my query...!!

      WITH RANGE
               AS (SELECT A.MASTERMACHINEID,
                          a.startdate,
                          a.enddate,
                          a.startdate - (1 / 3) + (lvl) * 1 / 3 SHIFT_ST_DT,
                          a.startdate + (lvl) * 1 / 3 AS SHIFT_END_DT,
                          a.quantity,
                          (LEAST ( enddate, TODATE) - GREATEST ( FROMDATE, startdate)) * 24 TOTAL_HRS,
                          (enddate - startdate) * 24 AVAIL,
                         todate,
                         fromdate
                    FROM OMP A,
                         (SELECT LEVEL lvl
                            FROM (SELECT MAX (enddate - startdate) AS diff FROM OMPWORKORDER)
                          CONNECT BY LEVEL <= (diff) * 3),
                         MASTER B
                   WHERE A.MASTERMACHINEID = B.MASTERMACHINEID 
                     AND lvl / 3 <=(enddate - startdate) + 1
                  ORDER BY SHIFT_ST_DT)
         SELECT shift_date,
                shift_num,
                shift_hrs,
                DECODE (SIGN (SHUT_DWN_TIME), -1, 0, SHUT_DWN_TIME),
                8 - DECODE (SIGN (SHUT_DWN_TIME), -1, 0, SHUT_DWN_TIME) shift_avail_hrs,
                qty,
                total_qty
           FROM (SELECT TRUNC (SHIFT_ST_DT) shift_date,
                        ROW_NUMBER () OVER (PARTITION BY TRUNC (SHIFT_ST_DT) ORDER BY SHIFT_ST_DT) shift_num,
                        8 shift_hrs,
                        (LEAST ( SHIFT_END_DT, TODATE) - GREATEST ( FROMDATE, SHIFT_ST_DT)) * 24
                          SHUT_DWN_TIME,
                        quantity / (avail - TOTAL_HRS) qty,
                        round(((SHIFT_END_DT - SHIFT_ST_DT) * 24 - (LEAST (SHIFT_END_DT, TODATE) - GREATEST (FROMDATE, SHIFT_ST_DT)) * 24)  * QuantiTY / (AVAIL - TOTAL_HRS),2)
                           TOTAL_QTY
                   FROM RANGE A );
      Regards
      KPR

      Edited by: BluShadow on 17-Mar-2011 09:48
      added {noformat}
      {noformat} tags for readability                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
        • 1. Re: how to create a view  with "WITH CLAUSE"
          BluShadow
          Is the error occurring when you attempt to create the view or with the query itself?

          If it's when you create the view, ensure you have the with clause within the view and not before it...
          SQL> create view vw_emp as
            2  with t as (select empno, ename, deptno from emp)
            3  select * from t;
          
          View created.
          
          SQL> select * from vw_emp;
          
               EMPNO ENAME          DEPTNO
          ---------- ---------- ----------
                7369 SMITH              20
                7499 ALLEN              30
                7521 WARD               30
                7566 JONES              20
                7654 MARTIN             30
                7698 BLAKE              30
                7782 CLARK              10
                7788 SCOTT              20
                7839 KING               10
                7844 TURNER             30
                7876 ADAMS              20
                7900 JAMES              30
                7902 FORD               20
                7934 MILLER             10
          
          14 rows selected.
          • 2. Re: how to create a view  with "WITH CLAUSE"
            647939
            Try creating view on following query, if it can help you:
            SELECT shift_date,
                shift_num,
                shift_hrs,
                decode(SIGN(shut_dwn_time),     -1,     0,     shut_dwn_time),
                8 -decode(SIGN(shut_dwn_time),     -1,     0,     shut_dwn_time) shift_avail_hrs,
                qty,
                total_qty
            FROM
                (
                    SELECT TRUNC(shift_st_dt) shift_date,
                         row_number() over(PARTITION BY TRUNC(shift_st_dt)
                     ORDER BY shift_st_dt) shift_num,
                         8 shift_hrs,
                        (least(shift_end_dt,      todate) -greatest(fromdate,      shift_st_dt)) *24 shut_dwn_time,
                         quantity /(avail -total_hrs) qty,
                         ROUND(((shift_end_dt -shift_st_dt) *24 -(least(shift_end_dt,      todate) -greatest(fromdate,      shift_st_dt)) *24) *quantity /(avail -total_hrs),      2) total_qty
                     FROM 
                     (
                          SELECT a.mastermachineid,
                             a.startdate,
                             a.enddate,
                             a.startdate -(1 / 3) +(lvl) *1 / 3 shift_st_dt,
                             a.startdate +(lvl) *1 / 3 AS
                         shift_end_dt,
                             a.quantity,
                            (least(enddate,      todate) -greatest(fromdate,      startdate)) *24 total_hrs,
                            (enddate -startdate) *24 avail,
                             todate,
                             fromdate
                         FROM omp a,
                                (SELECT LEVEL lvl
                             FROM
                                (SELECT MAX(enddate -startdate) AS
                                diff
                                 FROM ompworkorder)
                            CONNECT BY LEVEL <=(diff) *3),
                             master b
                         WHERE a.mastermachineid = b.mastermachineid
                         AND lvl / 3 <=(enddate -startdate) + 1
                         ORDER BY shift_st_dt
                     ) a
                 )
            ;
            Regards,
            Dipali.l
            • 3. Re: how to create a view  with "WITH CLAUSE"
              KPR
              Thanks a lot...working fine now..!! created view.

              Regards
              KPR
              • 4. Re: how to create a view  with "WITH CLAUSE"
                BluShadow
                So what was the actual problem? What it a problem with the query rather than creating a view?