How to write code in Perl that updates an Oracle SQL table with the running total and sum of two columns?

Question

I'm very unfamiliar the Perl programming language and was given the task of populating an Oracle SQL table with the running total and sum of columns DEBIT and CREDIT. When I try loading in the test file csv, the running total and sum columns do not load in properly.

I've tried creating a subroutine for populating the running total and sum_of_debit credit column:

sub running total
{
my $sql=<<UPDATESQL;
    UPDATE liq.ecb_stat s
    SET sum_of_debit_credit= debit-credit
    WHERE s.sum_of_debit_credit IS NULL
;

    UPDATE liq.ecb_stat s
    SET running_total= {SELECT SUM(sum_of_debit_credit) OVER(ORDER BY settlement_time_stamp)
    FROM liq.ecb_stat s2 
    WHERE s.settlement_time_stamp=s2.settlement_time_stamp)
    where s.running_total IS NULL
    UPDATESQL
}

Ideally this should return the correct values, but no luck so far.

Answer 1

Your SQL is not valid (we don't use { } but ( ) for subqueries, and the OVER clause would return multiple rows which violates the rule that a subquery in an = expression must return only one row, so I know you haven't actually executed these updates yet.

If you have a Perl issue, others can help with that to get you to the point where you're actually sending a call to the database. But once you get that figured out, you'll need to address the bad SQL as well, and that's an Oracle question.

To get a running total you have to use the ROWS BETWEEN windowing within your aggregation, and to get reasonable performance you should not attempt to do that in a subquery of the SET clause at all. You're better off with a MERGE, like this:

MERGE INTO liq.ecb_stat tgt
USING (SELECT ROWID row_id,
              SUM(sum_of_debit_credit) OVER (ORDER BY settlement_time_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
         FROM liq.ecb_stat) src
   ON (tgt.ROWID = src.row_id)
 WHEN MATCHED THEN UPDATE SET tgt.running_total = src.running_total

You can also combine the two operations into one:

MERGE INTO liq.ecb_stat tgt
USING (SELECT ROWID row_id,
              debit - credit sum_of_debit_credit,
              SUM(debit - credit) OVER (ORDER BY settlement_time_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
         FROM liq.ecb_stat) src
   ON (tgt.ROWID = src.row_id)
 WHEN MATCHED THEN UPDATE tgt.running_total = src.running_total,
                          tgt.sum_of_debit_credit = src.sum_of_debit_credit