Hello All,
I have written a stored procedure in Mysql Database of version 5.1.36-community-log.
I had tested the procedure and it tested fine. All of a sudden I got this error today. I am a bit confused as I have two select statements on one table but they individually return single rows.
1) I have three types of dumpster in one table . I have to sum-up the value of two dumpsters(ID 1 and 3) and have to enter the data of the third dumpster(ID 2 ) as it is.
I have used a number of variables to store the selected columns by the two individual queries and then I have an insert statement.
I hope I can get some logical answer as I am confused here.
HERE IS MY PROCEDURE
Code:
DROP PROCEDURE `PhysicalPlantCalculatedColumns`//
CREATE DEFINER=`ssingh39`@`%` PROCEDURE `PhysicalPlantCalculatedColumns`(IN MONTH VARCHAR(45),IN YEAR_1 YEAR)
BEGIN
DECLARE ROLL_OFF_OTC_TOTAL,ROLL_OFF_OTC_INERT,NRW_RSI,CONCRETE_RSI,INERT_RSI,WOOD_RSI,METAL_RSI,BRICK_RSI,
MISC_RECYCLABLE_RSI,CARDBOARD_RSI,DRYWALL_RSI DECIMAL(20,4) DEFAULT 0.0000;
SELECT TONNAGE_YARD,INERT
INTO ROLL_OFF_OTC_TOTAL,ROLL_OFF_OTC_INERT
FROM PhysicalPlanReportTable
WHERE DumpsterType = 2
AND Year = YEAR_1
AND Month = MONTH;
SELECT SUM(NONRECYCEABLE),SUM(CONCRETE),SUM(INERT),SUM(WOOD),SUM(METAL),SUM(BRICK),SUM(MISC_RECYCLABLE),SUM(CARDBOARD),SUM(DRYWALL)
INTO NRW_RSI,CONCRETE_RSI,INERT_RSI,WOOD_RSI,METAL_RSI,BRICK_RSI,MISC_RECYCLABLE_RSI,CARDBOARD_RSI,DRYWALL_RSI
FROM PhysicalPlanReportTable
WHERE DumpsterType IN (1,3)
AND Year = YEAR_1
AND Month = MONTH;
INSERT INTO Recycling_Rates_Calculated_Columns_PhysicalPlant(Month,Year,ROLL_OFF_OTC_TOTAL,ROLL_OFF_OTC_INERT,NRW_RSI, CONCRETE_RSI,INERT_RSI,WOOD_RSI,METAL_RSI,BRICK_RSI,MISC_RECYCLABLE_RSI,CARDBOARD_RSI,DRYWALL_RSI)
VALUES(MONTH,YEAR_1,ROLL_OFF_OTC_TOTAL,ROLL_OFF_OTC_INERT,NRW_RSI,CONCRETE_RSI,INERT_RSI,WOOD_RSI,METAL_RSI,BRICK_RSI,
MISC_RECYCLABLE_RSI,CARDBOARD_RSI,DRYWALL_RSI);
END
dBforums
>
ERROR CODE 1172 Result consisted of more than one row
08-02-11, 13:35
Linear Mode
