I'm trying to create a report procedure. I created a report table, liqReports and I need columns/data from 3 other tables. This is what I have done, it's wrong. When I run the 3 selects alone, I get the correct data. I thought I could just do 3 inserts, then I thought I could do updates. Please advise.
Code:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `liqReport`()
BEGIN
truncate table liqReports;
create table if not exists liqReports (face_amt decimal(12,2), pending_amt decimal(12,2), posted_amt decimal(12,2), portfolio_name varchar(24),place_date date);
insert into liqReports(face_amt, pending_amt, posted_amt, portfolio_name, place_date)
select 0, 0, sum(p.paymentamount), pf.portfolio, pf.placementdate from payments p,portfolios pf where p.portfolio=pf.portfolio and p.paymentstatus='POSTED'
group by pf.portfolio order by pf.placementdate;
update liqReports set pending_amt=(
select sum(p.paymentamount) from payments p,liqReports lr where p.portfolio=lr.portfolio_name and p.paymentstatus='PENDING'
group by lr.portfolio_name order by lr.place_date);
insert into liqReports(face_amt, pending_amt, posted_amt, portfolio_name, place_date)
select sum(d.originalbalance), lr.pending_amt, lr.posted_amt, lr.portfolio_name, lr.place_date from dbase d,liqReports lr where d.portfolio=lr.portfolio_name
group by lr.portfolio_name order by lr.place_date;
END