Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

new table created from columns of multiple tables

Status
Not open for further replies.

ksbrace

Programmer
May 13, 2000
501
US
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

 
Ok, I made some progress...but not giving me the correct results. I'm sure I need another nested loop, but I think I better get one nested loop working first. Any help would be great!!! Thanks in advance.
'1435.94', '3339.09', NULL, '20121030AFF', '2012-10-30'
'1435.94', '3308.76', NULL, '20121031Citi', '2012-10-31'
'1435.94', '13218.77', NULL, '20121031CNG', '2012-10-31'
'1435.94', '13218.77', NULL, '20121031CNG', '2012-10-31'

Code:
-- -------------------------------------------------------------------------------- 
-- Routine DDL 
-- Note: comments before and after the routine body will not be stored by the server 
-- -------------------------------------------------------------------------------- 
DELIMITER $$ 

CREATE PROCEDURE `collectionsmax`.`liqReports` () 
BEGIN 


Declare donePost boolean default 0; 
declare donePend boolean default 0; 
declare postAmt decimal(10,2); 
declare pendAmt decimal(10,2); 
declare faceAmt decimal (10,2) default 0; 
declare portName varchar(25); 
declare placeDate date; 

declare posted cursor 
for 
select 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; 

declare pend cursor 
for 
select sum(p.paymentamount), pf.portfolio, pf.placementdate from 
payments p,portfolios pf where p.portfolio=pf.portfolio and 
p.paymentstatus='PENDING' 
group by pf.portfolio order by pf.placementdate; 

DECLARE CONTINUE HANDLER FOR not found set donePost:=true; 
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); 

open posted; 

postedLoop: loop 

fetch posted into postAmt, portName, placeDate; 

if donePost then 
close posted; 
leave postedLoop; 
end if; 

open pend; 

pendLoop: loop 

fetch pend into pendAmt, portName, placeDate; 

insert into liqReports (face_amt, pending_amt, posted_amt, portfolio_name, place_date) 
values(faceAmt, pendAmt, postAmt, portName, placeDate); 

if donePost then set donePend:=false; 
close pend; 
leave pendLoop; 
end if; 
end loop pendLoop; 
end loop postedLoop; 

END
[code]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top