Greetings.
I have 1 procedure that summarises data in a table for one particular
location. It accepts the location ID, and returns the summary as a
single record. It works well.
Now I'm trying to write a procedure that selects all location IDs, and
calls this 1st procedure for every single location, outputting a summary
record per location.
Here is this procedure:
---
CREATE [EMAIL PROTECTED] PROCEDURE `sp_GasPostingSummary`()
BEGIN
declare done int default 0;
declare LocID int;
declare my_locations cursor for select LocID from GasLocations;
declare continue handler for not found set done = 1;
open my_locations;
repeat
fetch my_locations into LocID;
if not done then
call sp_GasProjectedAnnualConsumption( LocID );
end if;
until done end repeat;
close my_locations;
END
---
When I call it, instead of getting the results, I get lots of empty
sets:
Empty set (0.04 sec)
Empty set (0.04 sec)
Empty set (0.04 sec)
How do I get this above procedure to grab results from the 1st one
( below ), and output them to the client?
Here's the 1st procedure:
---
CREATE [EMAIL PROTECTED] PROCEDURE
`sp_GasProjectedAnnualConsumption`( in GasLocationID int )
BEGIN
/* Declare variables */
declare MaxBillDate date;
declare TotalConsumption double;
declare TotalSpend double;
declare TotalDays int;
/* Get the last bill date */
select max(BillDate) from GasPosting GP inner join GasAccountNo GA
on GP.AcctNoID = GA.AcctNoID
where GA.LocID = GasLocationID into MaxBillDate;
/* Get consumption data for a year prior to this date */
select
sum( TotalUnits ) /
case
when UnitType = 1 then 1000 /* Megajoules */
when UnitType = 2 then 1 /* Gigajoules */
when UnitType = 3 then 0.0036 /* kWh */
else 1 /* ? */
end as SumTotalConsumption,
sum( TotalBill ) as SumTotalBill,
sum( case when Days > 0 then Days else 28 end ) as SumTotalDays
from GasPosting GP inner join GasAccountNo GA
on GP.AcctNoID = GA.AcctNoID
where LocID = GasLocationID
and BillDate between date_sub( MaxBillDate, interval 1 year ) and
MaxBillDate
into TotalConsumption, TotalSpend, TotalDays;
/* Stretch to 365 days */
select
concat_ws( ', ', Address, Suburb ) as FullAddress,
MIRN,
round( TotalConsumption * 365 / TotalDays ) as
ProjectedAnnualConsumption,
round( TotalSpend * 365 / TotalDays ) as ProjectedAnnualSpend
from
GasLocations
where
LocID = GasLocationID;
END
---
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
