Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Running subtraction in calculated column.

mlov83Apr 1 2020 — edited Apr 1 2020

Because of the large decimal places in my percent column when I try to simply multiply the percent times the amount paid the net result ends up with an extra penny. I'm trying to write a query to keep track for the subtraction in a separate column. In that way I can simply distribute the difference to the last amount. Here is what I have so far.

CREATE TABLE MY_TEST

   ( USER_ID VARCHAR2(600 BYTE),

USER_DETAIL_CODE VARCHAR2(100 BYTE),

DETAIL_CODE VARCHAR2(4 CHAR) NOT NULL ENABLE,

PERCENT NUMBER(8,5),

USER_AMOUNT_PAID NUMBER,

WORKING_PERCENT NUMBER

   )

Insert statement.

REM INSERTING into MY_TEST

SET DEFINE OFF;

Insert into MY_TEST (USER_ID,USER_DETAIL_CODE,DETAIL_CODE,PERCENT,WORKING_PERCENT,USER_AMOUNT_PAID) values ('POP','F821','F821',7.14,0.0714,150);

Insert into MY_TEST (USER_ID,USER_DETAIL_CODE,DETAIL_CODE,PERCENT,WORKING_PERCENT,USER_AMOUNT_PAID) values ('POP','F821','F821',10.47,0.1047,150);

Insert into MY_TEST (USER_ID,USER_DETAIL_CODE,DETAIL_CODE,PERCENT,WORKING_PERCENT,USER_AMOUNT_PAID) values ('POP','F821','F821',62.39,0.6239,150);

Insert into MY_TEST (USER_ID,USER_DETAIL_CODE,DETAIL_CODE,PERCENT,WORKING_PERCENT,USER_AMOUNT_PAID) values ('POP','F821','F821',20,0.2,150);

.

    

USER_IDUSER_DETAIL_CODEDETAIL_CODEPERCENTUSER_AMOUNT_PAIDWORKING_PERCENTPAID_TIMES_PERCENTRUNNING_SUBTRACTION
POPF821F8217.141500.071410.71139.29
POPF821F82110.471500.104715.71123.58
POPF821F82162.391500.623993.5929.99
POPF821F821201500.230-0.01

the running subtraction column is simply subtracting (10.71 - 150) which equals 123.58 than (139.29 -15.71) than (123.58 -93.59)

my goal is to essentially replace the 30 with 29.99 since otherwise my summation is of by .01 .

SELECT

    user_id,

    user_detail_code,

    detail_code,

    percent,

    user_amount_paid,

    working_percent,

    round((working_percent * user_amount_paid),2) paid_times_percent,

    ''running_subtraction

FROM

    my_test;

I've written the above query but I just can't seem to come up with the right syntax for the running subtraction.

Any help would be appreciated.

This post has been answered by mathguy on Apr 1 2020
Jump to Answer
Comments
Post Details
Added on Apr 1 2020
3 comments
3,502 views