1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Recursive Sub Queries

Discussion in 'SQL PL/SQL' started by m2inet, Oct 25, 2010.

  1. m2inet

    m2inet Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

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

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    How can anyone provide a solution without knowing your table structure? Also as a policy we do not encourage "Here's the problem, solve it" kind of questions. Please do the following:

    1. Provide your create table structures

    2. Show us your attempted code, and we will help improve/correct it.
     
  3. m2inet

    m2inet Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Sorry i missed to attach the code.
    This is the code we tried in DB2, and it works fine. But not able to produce the oracle equivalent of this.

    Actual Table:

    SELECT *
    FROM CER_MKTG.CAP_CONSUMPTION
    ORDER BY 1
    ;

    ROW_NUM DEMAND CAPACITY
    1 10 15
    2 12 20
    3 20 15
    4 13 15
    5 19 15
    6 10 100



    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
    ;

    OUTPUT:

    ROW_NUM DEMAND ACTUAL_DEMAND BKLG CAPACITY
    1 10 10 0 15
    2 12 12 0 20
    3 20 20 5 15
    4 13 18 3 15
    5 19 22 7 15