merge into carpt.T_FT_WGJYL t
using (
select trunc(gpo.creationdate) creationdate,substr(gpo.memberacctcode,1,11) membercode,gpo.bosstype,sum(gpo.realamount)/1000 successfulamount from T_D_GATEWAYPAYORDER gpo
where
gpo.creationdate >= v_s_date
and gpo.creationdate < v_e_date
and gpo.orderstatus=111
and gpo.cardno is not null
group by trunc(gpo.creationdate),gpo.memberacctcode,gpo.bosstype
) s
ON (t.datetime=s.creationdate and t.membercode=s.membercode and t.bosstype=s.bosstype)
WHEN MATCHED THEN
UPDATE
Set t.successfulamount=s.successfulamount
WHERE t.datetime=s.creationdate and t.membercode=s.membercode and t.bosstype=s.bosstype
WHEN NOT MATCHED THEN
INSERT
VALUES(s.creationdate,s.membercode,s.bosstype,s.successfulamount)
;
08-21
08-21
08-21
08-21
08-21