Recursive Sub Query

We have to prepare a report like this.

Column 1 : Factory
Column 2 : Capacity
Column 3 : Demand
Column 4 : Actual Demand (Demand for Current Factory + Backlog from the previous Factory)
Column 3 : Backlog (If (Capacity < ActualDemand) then (Capacity - Actual Demand) else 0)

Sample Report like :

Factory Capacity Demand ActualDemand Backlog
1 100 80 80 0
2 50 40 40 0
3 100 150 150 50
4 30 20 70 40 ActualDemand=70(20 Current + 50 previous "Backlog")
5 80 30 70 0 ActualDemand=70(30 Current + 40 previous "Backlog")
6 100 70 70 0

In the above report, we have to include the previous rows' backlog quantity for calculating "Actual Demand" of the current row.
I have a rough idea of using recursive sub queries, but not an expert.
Can you provide a SQl solution for this.
Would be really helpful.

To get the previous backlog, you can use the LAG() analytical function.
;)

Code:

---

SQL> select factory, capacity, demand, actualdemand, backlog,
  2  --lag one row behind, return null if nothing found
  3        demand + lag(backlog, 1, null) OVER
  4        (order by factory) revised_demand
  5  from lagdemo order by factory;

  FACTORY  CAPACITY    DEMAND ACTUALDEMAND    BACKLOG REVISED_DEMAND
---------- ---------- ---------- ------------ ---------- --------------
        1        100        80          80          0
        2        50        40          40          0            40
        3        100        150          150        50            150
        4        30        20          70        40            70
        5        80        30          70          0            70
        6        100        70          70          0            70

---

thanks a lot LKBrwn_DBA & stecal for the reponse.
Unfortunately i am not able to use the LAG function in Cognos reporting tool.

I tried the following query from my friend, this works in DB2, but not in oracle.

WITH T1 (ROW_NUM, DEMAND, ACTUAL_DEMAND, BKLG, CAPACITY, DUMMY_ROW_NUM) AS
(
SELECT ROW_NUM
,DEMAND
,DEMAND AS ACTUAL_DEMAND
,CASE WHEN CAPACITY >= DEMAND THEN 0 ELSE DEMAND - CAPACITY END AS BKLG
,CAPACITY
,ROW_NUM + 1
FROM CER_MKTG.CAP_CONSUMPTION
WHERE ROW_NUM = 1

UNION ALL

SELECT A.ROW_NUM
,A.DEMAND
,A.DEMAND + B.BKLG AS ACTUAL_DEMAND
,CASE WHEN A.CAPACITY >= (A.DEMAND + B.BKLG) THEN 0 ELSE (A.DEMAND + B.BKLG) - A.CAPACITY END AS BKLG
,A.CAPACITY
,A.ROW_NUM + 1
FROM CER_MKTG.CAP_CONSUMPTION A
,T1 B
WHERE A.ROW_NUM = B.DUMMY_ROW_NUM
AND A.ROW_NUM > 1

)
SELECT ROW_NUM
,DEMAND
,ACTUAL_DEMAND
,BKLG
,CAPACITY
FROM T1
ORDER BY 1;

can you help with a similar suggestion in Oracle?

Quote:

---

Originally Posted by m2inet

...
Unfortunately i am not able to use the LAG function in Cognos reporting tool.
...

---

Yes you are able to use it if you create a function or procedure that returns a ref cursor.
;)