INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

new table created from columns of multiple tables

new table created from columns of multiple tables

new table created from columns of multiple tables

(OP)
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 

RE: new table created from columns of multiple tables

(OP)
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]

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close