Recursive Sub Query

[m2inet]

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.

[LKBrwn_DBA]

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

[stecal]

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

[m2inet]

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?

[LKBrwn_DBA]

...
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.