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
