Loop through date range

Question

How to loop Oracle query through the date? I have to put variable in 4 place. My query start with WITH AS, so I can't use Oracle SQL Loop through Date Range solution.

I also can't create temporary table.

Here is my attempt:

   WITH d 
AS (
    SELECT DATE'2015-06-22' + LEVEL - 1 AS current_d
       FROM dual
    CONNECT BY DATE'2015-06-22' + LEVEL - 1 < DATE'2015-10-04'
),
OrderReserve
AS (
    SELECT cvwarehouseid
        ,lproductid
        ,SUM(lqty) lqty
    FROM ABBICS.iOrdPrdQtyDate
    GROUP BY cvwarehouseid
        ,lproductid
    )
    SELECT 
    ...
    WHERE IORDREFILL.DNCONFIRMEDDELDATE < CAST(TO_CHAR(d.current_d , 'YYYYMMDD') AS NUMBER(38))
    ...

Check this: http://stackoverflow.com/questions/987610/how-to-iterate-over-a-date-range-in-pl-sql — Rahul Tripathi, Oct 05 '15 at 09:39
In the provided example you woudn't need a date range. Just check that `IORDREFILL.DNCONFIRMEDDELDATE` is smaller than the highest date in the range and you'll catch all rows in one sweep. — Martin Schapendonk, Oct 05 '15 at 09:55
@Kulis that only defines a "new" table name to use in the final query. Did you mention it in your FROM clause? — Martin Schapendonk, Oct 05 '15 at 09:58
When I put d in FROM clause, I get error from join condition: FROM x.IORDREFILL, d LEFT JOIN x.IORDREFSTU ON IORDREFILL.LORDERID = IORDREFSTU.LORDERID AND IORDREFILL.CVWAREHOUSEID = IORDREFSTU.CVWAREHOUSEID ->ORA-00904: "IORDREFILL"."LPRODUCTID": invalid identifier — Kulis, Oct 05 '15 at 10:06

Martin Schapendonk · Answer 1 · 2015-10-05T09:58:36.357

If I understand you correctly, you assume that you can only use 1 inline table per query. That is not true, you can use multiple inline tables and expand the existing WITH clause with another to loop through dates:

with OrderReserve as (
  SELECT cvwarehouseid
      ,lproductid
      ,SUM(lqty) lqty
  FROM ABBICS.iOrdPrdQtyDate
  GROUP BY cvwarehouseid
      ,lproductid    
), date_range as (
  select sysdate+level
  from dual
  connect by level <= 30
)
select *
from OrderReserve, date_range
... -- expand with date_range as you see fit
;

Loop through date range

1 Answers1