All,
The query below is designed to insert into a table. This works when I have a single loan which I insert. However, if remove the part of the where clause of a single loan the insert does not work. The table fnmloan is a large table with 500mm + rows and the query runs for about 4 hours. Any idea of how to get this to work? I am a little stumped since the query works with one loan.
Glenn
SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;
truncate fnmloan_balance;
insert into fnmloan_balance (
fctrdt, loanseqnum, secmnem, beginbal, scheduled,endbal,smm
)
select
fnmloan.fctrdt
,fnmloan.loanseqnum
,fnmloan.secmnem
--,fnmloan.orignoterate
--,fnmloan.loanage
--,fnmloan.origloanamt
,fnmloan.currrpb as beginbal
,round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
cast(fnmloan.remterm - 1 as numeric),
cast(fnmloan.currrpb as numeric)),4)) as scheduled
,coalesce(endbal.currrpb,0) as endbal
,abs(round(
cast((fnmloan.currrpb - coalesce(endbal.currrpb,0) - round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
cast(fnmloan.remterm - 1 as numeric),
cast(fnmloan.currrpb as numeric)),4)) )/(fnmloan.currrpb - round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
cast(fnmloan.remterm - 1 as numeric),
cast(fnmloan.currrpb as numeric)),4)) ) as numeric)
,4)) as SMM
from
(
select * from fnmloan
where
fctrdt < '03-01-2019'
and
loanseqnum = '5991017042'
) as fnmloan
left outer join
(select
fctrdt - interval '1 month' as fctrdt
,loanseqnum
,orignoterate
,loanage
,origloanamt
,currrpb
from fnmloan
) as endbal
on fnmloan.loanseqnum = endbal.loanseqnum
and fnmloan.fctrdt = endbal.fctrdt
