Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: ERROR CODE 1172 Result consisted of more than one row

    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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    One question, what is the primary key for the table PhysicalPlanReportTable? I suspect that this is the source of the problem. Is there a unique key or primary key on DumpsterType, Year and Month?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    Thank you for the suggestion. I have not been able to get back to this problem. I will update this thread when I test the Stored Procedure .

    Marisha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •