Recursive Sub Query

# Thread: Recursive Sub Query

1. Junior Member
Join Date
Oct 2010
Posts
2

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

2. ## Check out the SQL Language Reference

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

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

4. Junior Member
Join Date
Oct 2010
Posts
2
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?

5. ## REF CURSOR anyone?

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Click Here to Expand Forum to Full Width