INSERT INTO TRANSLED
VALUES ('0003', '176', '111111111','Di',-1.02)1 row(s) inserted.
INSERT INTO TRANSLED
VALUES ('0003', '176', '111111111','Di',-20.03)1 row(s) inserted.
INSERT INTO TRANSLED
VALUES ('0003', '179', '222222222', 'Di',-1.15)1 row(s) inserted.
INSERT INTO TRANSLED
VALUES ('0003', '179', '222222222', 'Di',-15.10)1 row(s) inserted.
CREATE TABLE TRANS (
PLANID VARCHAR2(9),
TRANSID VARCHAR2(16),
ACTIVITYFEEAMT NUMBER(15,2)
)Table created.
INSERT INTO TRANS
VALUES ('0003', '176', 25.00)1 row(s) inserted.
INSERT INTO TRANS
VALUES ('0003', '179', 25.00)1 row(s) inserted.
INSERT INTO TRANS
VALUES ('0003', '185', 25.00)1 row(s) inserted.
CREATE TABLE TRANSLED (
PLANID VARCHAR2(9),
TRANSID VARCHAR2(16),
SSNUM VARCHAR2(9),
TRANSTYPECD CHAR(2),
TOTALDOLAMT NUMBER(15, 2)
)Table created.
INSERT INTO TRANSLED
VALUES ('0003', '185', '333333333', 'Di',-25.10)1 row(s) inserted.
INSERT INTO TRANSLED
VALUES ('0003', '185', '333333333', 'Di',-15.10)1 row(s) inserted.
select * from trans| PLANID | TRANSID | ACTIVITYFEEAMT | 0003 | 176 | 25 | 0003 | 179 | 25 | 0003 | 185 | 25 |
|---|
select * from transled| PLANID | TRANSID | SSNUM | TRANSTYPECD | TOTALDOLAMT | 0003 | 176 | 111111111 | Di | -1.02 | 0003 | 176 | 111111111 | Di | -20.03 | 0003 | 179 | 222222222 | Di | -1.15 | 0003 | 179 | 222222222 | Di | -15.1 | 0003 | 185 | 333333333 | Di | -25.1 | 0003 | 185 | 333333333 | Di | -15.1 |
|---|
update trans a
set activityfeeamt =
(
select sum(tl.totaldolamt * -1)Fee
from transled tl
where a.transid = tl.transid and a.planid = tl.planid
and tl.planid = '0003' and tl.transtypecd = 'Di'
group by tl.transid, tl.planid, tl.ssnum
having sum(tl.totaldolamt * -1) < (select t.activityfeeamt from trans t where t.planid = '0003' and tl.transid = t.transid)
);