Cursor to Query statement

Question

I am inserting into TABLE_A as given below.

INSERT 
       INTO Table_A (house_id,
                    house_key_nbr,
                    mnty_code,
                    split) 
     SELECT wcp.id,
            ld.ld_ln_id, 
            ld.ld_mnty,           
            ROUND((ld.ld_ln_bal/wla.LOAN_AMT) * 100,2) split
       FROM table_B ld,
                 table_C cc,
                 TABLE_D wcp, 
                 TABLE_E wla    
      WHERE cc.conv_id         = I_conv_id
        AND cc.ev_id           = wcp.ev_id 
        AND cc.client_plan_nbr = ld.plan_id
        AND wcp.ssn            = ld.ssn
        AND wla.house_id        = wcp.id
        AND wla.house_key_nbr  = ld.ld_ln_id
        AND ld.status_code in ('V','W');
     

Once i have loaded into the table_A then i created a cursor to find out the records having the sum of split not equal to 100. For those cases I will find the diff and then update the record as given below.

CURSOR max_percent IS                                                     
   SELECT house_id,                                                           
          house_key_nbr,                                                     
          sum(split) percent_sum                                     
     FROM TABLE_A s1,                             
          TABLE_D p1,                                            
          table_C c1                                               
    WHERE s1.house_id = p1.id                                           
      AND p1.ev_id = c1.ev_id                                            
      AND c1.conv_id = I_conv_id                                       
    GROUP BY house_id, house_key_nbr                                    
   HAVING SUM(split) != 100;         
         
     OPEN max_percent;

     l_debug_msg:='Cursor Opened';
     
     FETCH max_percent BULK COLLECT INTO mnty_rec;  
     
     l_debug_msg:='Fetching the values from cursor';
     
     FOR i    IN 1..mnty_rec.COUNT
     LOOP
         v_diff := 100.00 - mnty_rec(i).percent_sum; 
         l_debug_msg:='The difference is '||v_diff||' for the house_id : '||mnty_rec(i).house_id;

         UPDATE work_conv_part_loan_mnty_splt wcplms       
            SET split        = split + v_diff       
          WHERE wcplms.house_id       = mnty_rec(i).house_id                 
            AND wcplms.house_key_nbr = mnty_rec(i).house_key_nbr              
            AND rownum = 1;  

         l_debug_msg:='Updated the percentage value for the house_id'||mnty_rec(i).house_id ; 
     END LOOP;
     CLOSE max_percent;
     

The question here is, I achieved this simple process using a cursor. Is there any way I can achieve it during the insertion time itself instead of writing the cursor?

Answer 1

If mnty_code is unique for each house_id, house_key_nbr pair, then you can use window functions in your insert. Try using this for inserting into the split column:

        CASE WHEN 1 = ROWNUMBER() OVER ( PARTITION BY wcp.id, ld.ld_ln_id ORDER BY mnty_code DESC ) THEN
          -- This is the last row for the given house_id / house_key_nbr, so do special split calculation
          100 - SUM(ROUND((ld.ld_ln_bal/wla.LOAN_AMT) * 100,2)) OVER ( PARTITION BY wcp.id, ld.ld_ln_id ORDER BY mnty_code ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) ELSE
         -- Normal split calculation
           ROUND((ld.ld_ln_bal/wla.LOAN_AMT) * 100,2) 
           END split

The idea is that, if you are inserting the last row for a given house_id, house_key_nbr, then set the split value to 100 minus the sum of all the previous values.

If mnty_code is not unique within each house_id, house_key_nbr pair, it gets problematic, because there is no way to identify the "last" row in each group.

Answer 2

I'm simplifying a bit your setup with two tables: table_a accumulation the data and table_b containing new data.

-- TABLE_A: Primary Key  HOUSE_ID, HOUSE_KEY_NBR
create table table_a as
select 1 house_id, 1 house_key_nbr, 90 split from dual union all
select 1 house_id, 2 house_key_nbr, 30 split from dual union all
select 1 house_id, 3 house_key_nbr, 100 split from dual;

-- TABLE_B: new data
create table table_b as
select 1 house_id, 1 house_key_nbr, 5 split from dual union all
select 1 house_id, 1 house_key_nbr, 5 split from dual union all
select 1 house_id, 4 house_key_nbr, 50 split from dual union all
select 1 house_id, 4 house_key_nbr, 40 split from dual union all
select 1 house_id, 5 house_key_nbr, 100 split from dual;

The important point is that the table_a has the primary key defined, so you need to update only one row for the correction of the SPLIT

The first step is simple to MERGE the new data

MERGE INTO table_a a
USING (select HOUSE_ID, HOUSE_KEY_NBR, sum(SPLIT) SPLIT
       from table_b
       group by HOUSE_ID, HOUSE_KEY_NBR) b
ON (a.HOUSE_ID = b.HOUSE_ID and a.HOUSE_KEY_NBR = b.HOUSE_KEY_NBR)
WHEN MATCHED THEN 
  update SET a.SPLIT = a.SPLIT + b.SPLIT
WHEN NOT MATCHED THEN
  insert (HOUSE_ID, HOUSE_KEY_NBR, SPLIT)
  values (b.HOUSE_ID, b.HOUSE_KEY_NBR, b.SPLIT)

So basically you first aggregates the new data to the level of the PK and than using the MERGE either insert or update the table_a

In the second step perform the correction using the same approach with MERGE only use a different source table containing only the defference of the SPLIT to 100.

MERGE INTO table_a a
USING (select HOUSE_ID, HOUSE_KEY_NBR, 100 - sum(SPLIT)  SPLIT
       from table_a
       group by HOUSE_ID, HOUSE_KEY_NBR 
       having sum(SPLIT) != 100) b
ON (a.HOUSE_ID = b.HOUSE_ID and a.HOUSE_KEY_NBR = b.HOUSE_KEY_NBR)
WHEN MATCHED THEN 
  update SET a.SPLIT = a.SPLIT + b.SPLIT
WHEN NOT MATCHED THEN
  insert (HOUSE_ID, HOUSE_KEY_NBR, SPLIT)
  values (b.HOUSE_ID, b.HOUSE_KEY_NBR, b.SPLIT)

After this step all SPLIT are equal 100

select HOUSE_ID, HOUSE_KEY_NBR,   sum(SPLIT)
from table_a
group by HOUSE_ID, HOUSE_KEY_NBR
order by 1,2;

  HOUSE_ID HOUSE_KEY_NBR SUM(SPLIT)
---------- ------------- ----------
         1             1        100
         1             2        100
         1             3        100
         1             4        100
         1             5        100

If you do not want to MERGE in table_a and you use INSERT only, I'd challange this desing, because it is not clear which of the many records with the same key you want to update.

I'll recomend not to UPDATE but to INSERT additional rows with the calculated differece SPLIT.