Recursive calculation in SQL (Oracle)

Question

I find it difficult to find an ETL solution for some data in my summary table. I think I cannot accomplish this using pure SQL, and I need to use PL-SQL because of the loop. Can sql gurus help me go in the right direction or provide some pointers to solve this problem?

Here's the scenario: Tables: TABLEA and TABLEB.

Steps:

• Group entries in TABLEA by A_CD and SUM field A_AMT. (Assume that A_FLAG is always the same for any A_CD.). Allows you to group the list of results as TABLEA_GRP (this is not a table, this is a grouped query).
• Select a row from TABLEB, and if B_FLG is "N", select all rows in TABLEA_GRP, where A_FLG is "N". If B_FLG is "Y", then select all rows in TABLEA_GRP.
• Starting the first record of the rows selected in step 2, calculate the ratio of its TOTAL_AMT to SUM for ALL TOTAL_AMT for the selected rows. Multiply the coefficient by B_AMT and add the total to the TOTAL_AMT lines and save in RESULTING_AMT. Repeat this calculation for all rows selected in step 2.
• Repeat steps 2 and 3, now using the initial TOTAL_AMT VALUE from the RESULTING_AMT value from the previous calculation of the same A_CD.

The RESULTING _RATIO field is not required for saving, it is simply indicated for demonstration purposes. How do you do this?

Basically I want to get the data in RESULTING_TABLE from TABLEA and TABLEB

Can anyone help? Thanks so much for any recommendations.

EDIT: I added A_DATE and B_DATE to support the join between the two tables. For simplicity, you can simply do A.A_DATE = B.B_DATE, for example, this is the base connection:

SELECT
  A.A_CD,
  SUM(A.A_AMT) AS TOTAL_AMT,
  A.A_FLAG,
  A.A_DATE,
  B.B_ID,
  B.B_AMT,
  B.B_FLAG
FROM
  TABLEA A
JOIN TABLEB B
ON A.A_DATE = B.B_DATE
GROUP BY
  A.A_CD,
  A.A_FLAG,
  A.A_DATE,
  B.B_ID,
  B.B_AMT,
  B.B_FLAG
;

Answer 1

, , . , , , . 1 2 (main_sql). 3 4 (recur_sql).

with main_sql as (
    select  a.*,
            b.*,
            sum(a_amt) over (partition by b_id) as cd_amt,
            rank() over (partition by a_cd order by b_id) as rnk
    from   (select a_cd, a_flag, sum(a_amt) as a_amt
            from   tablea
            group  by a_cd, a_flag) a,
            tableb b
    where   a.a_flag = case when b.b_flag = 'Y' then a.a_flag else b.b_flag end
    order by b_id, a_cd
), 
recur_sql (a_cd, b_id, total_amt, cd_amt, resulting_ratio, resulting_amt, rnk) as (
    select m.a_cd, 
           m.b_id, 
           m.a_amt as total_amt, 
           m.cd_amt, m.a_amt / m.cd_amt as resulting_ratio, 
           m.a_amt + (m.a_amt / m.cd_amt * m.b_amt) as resulting_amt, 
           rnk
    from   main_sql m
    where  rnk = 1

    union all

    select m.a_cd, 
           m.b_id, 
           r.resulting_amt as total_amt, 
           m.cd_amt, 
           r.resulting_amt / m.cd_amt as resulting_ratio, 
           r.resulting_amt + (r.resulting_amt / m.cd_amt * m.b_amt) as resulting_amt,
           m.rnk
    from recur_sql r,
         main_sql m
    where m.rnk > 1
    and   r.a_cd = m.a_cd
    and   m.rnk - 1 = r.rnk

)
select a_cd, b_id, total_amt, resulting_ratio, resulting_amt
from   recur_sql
order by 2, 1