Dividing a sum value into multiple rows due to field length constraint

I am migrating financial data from a very large table (100 million+ of rows) by summarizing the amount and insert them into summary table. I ran into problem when the summary amount (3 billions) is larger than what the field in the summary table can hold (can only hold up to 999 millions.) Changing the field size is not an option as it requires a change process.

The only option I have is to divide the amount (the one that breach the size limit) into smaller ones so it can be inserted into the table.

I came across this SQL – I need to divide a total value into multiple rows in another table which is similar except the number of rows I need to insert is dynamic.

For simplicity, this is how the source table might look like

account_table

acct_num   |      amt
-------------------------------
   101         125.00
   101         550.00
   101         650.00
   101         375.00
   101         475.00
   102          15.00
   103         325.00
   103         875.00
   104         200.00
   104         275.00

The summary records are as follows

select acct_num, sum(amt) 
from account_table
group by acct_num

Account Summary

acct_num   |      amt
-------------------------------
   101        2175.00
   102          15.00
   103        1200.00
   104         475.00

Assuming the maximum value in the destination table is 1000.00, the expected output will be

summary_table

acct_num   |      amt
-------------------------------
   101        1000.00
   101        1000.00
   101         175.00
   102          15.00
   103        1000.00
   103         200.00
   104         475.00

How do I create a query to get the expected result? Thanks in advance.

Answer

You need a numbers table. If you have a handful of values, you can define it manually. Otherwise, you might have one on hand or use a similar logic:

with n as (
      select (rownum - 1) as n
      from account_table
      where rownum <= 10
     ),
     a as (
      select acct_num, sum(amt) as amt
      from account_table
      group by acct_num
     ) 
select acct_num,
       (case when (n.n + 1) * 1000 < amt then 1000
             else amt - n.n * 1000
        end) as amt
from a join
     n
     on n.n * 1000 < amt ;

Advertisement

Answer