DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Recursive Sub Query

  1. #1
    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. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Check out the SQL Language Reference

    To get the previous backlog, you can use the LAG() analytical function.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    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. #4
    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. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool REF CURSOR anyone?

    Quote Originally Posted by m2inet View Post
    ...
    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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