Reputation: 11
I see this question has been asked before but no answer provided.
I am using a cursor to parse the data returned by a SELECT statement. However, it does not return the data I expect when compared to running the SELECT statement in a command line.
Broker Year Month Total
Houlder Insurance 2011 10 1
Houlder Insurance 2011 11 1
Houlder Insurance 2011 12 1
Incepta 2012 6 4
Incepta 2012 7 6
Incepta 2012 8 4
Kay International Plc 2011 10 4
Kay International Plc 2011 11 5
Kay International Plc 2011 12 2
Kay International Plc 2012 1 1
Kay International Plc 2012 2 1
Kay International Plc 2012 4 4
Kay International Plc 2012 5 6
Kay International Plc 2012 6 2
Kay International Plc 2012 7 1
Kay International Plc 2012 8 1
Miles Smith 2011 12 3
Miles Smith 2012 2 1
Miles Smith 2012 4 1
Miles Smith 2012 5 6
Miles Smith 2012 6 1
Miles Smith 2012 7 2
Miles Smith 2012 8 2
When I run the following stored procedure code, It appears to only to return the records relating to the brokers "Kay International" and "Incepta"! Is there a solution to this issue?
CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker TEXT;
DECLARE yy INT;
DECLARE jan INT;
DECLARE feb INT;
DECLARE mar INT;
DECLARE apr INT;
DECLARE may INT;
DECLARE jun INT;
DECLARE jul INT;
DECLARE aug INT;
DECLARE sep INT;
DECLARE toct INT;
DECLARE nov INT;
DECLARE tdec INT;
DECLARE db_broker TEXT;
DECLARE db_year INT;
DECLARE db_month INT;
DECLARE db_total INT;
DECLARE eof INT;
DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
, year(submit_date_time) AS 'Year'
, month(submit_date_time) AS 'Month'
, count(*) AS 'Total'
FROM
survey_request a
JOIN survey d
ON d.src_reference = a.src_reference
JOIN contacts_companies b
ON b.reference = a.broker_company_reference
JOIN src_status c
ON c.reference = a.src_status_reference
WHERE
underwriter_company_reference = 73
GROUP BY
broker
, year
, month
ORDER BY
broker
, year
, month;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;
SET broker = '';
SET yy = 0;
SET jan = 0;
SET feb = 0;
SET mar = 0;
SET apr = 0;
SET may = 0;
SET jun = 0;
SET jul = 0;
SET aug = 0;
SET sep = 0;
SET toct = 0;
SET nov = 0;
SET tdec = 0;
TRUNCATE TABLE reportmonthsurveysubmitted;
OPEN cur1;
SET eof = 0;
WHILE eof = 0
DO
FETCH cur1 INTO db_broker, db_year, db_month, db_total;
IF broker <> db_broker THEN
SET broker = db_broker;
SET yy = 0;
END IF;
IF yy <> db_year THEN
IF yy <> 0 THEN
INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
END IF;
SET yy = db_year;
SET jan = 0;
SET feb = 0;
SET mar = 0;
SET apr = 0;
SET may = 0;
SET jun = 0;
SET jul = 0;
SET aug = 0;
SET sep = 0;
SET toct = 0;
SET nov = 0;
SET tdec = 0;
END IF;
CASE db_month
WHEN 1 THEN
SET jan = db_total;
WHEN 2 THEN
SET feb = db_total;
WHEN 3 THEN
SET mar = db_total;
WHEN 4 THEN
SET apr = db_total;
WHEN 5 THEN
SET may = db_total;
WHEN 6 THEN
SET jun = db_total;
WHEN 7 THEN
SET jul = db_total;
WHEN 8 THEN
SET aug = db_total;
WHEN 9 THEN
SET sep = db_total;
WHEN 10 THEN
SET toct = db_total;
WHEN 11 THEN
SET nov = db_total;
WHEN 12 THEN
SET tdec = db_total;
END CASE;
END WHILE;
CLOSE cur1;
INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
END
Upvotes: 1
Views: 1350
Reputation: 5253
You have problem in your while loop. If you will trace, then you will get to know that insert query is not getting fired for "Houlder insurance" in while loop.
First time yy = 0, after setting broker = db_broker and yy = 0. Then, IF yy <> db_year THEN
evaluates TRUE, so it will check for yy <> 0, which it is, so making evaluation to FALSE. Then it sets yy = db_year, thus yy becomes 2011. Second time, broker <> db_broker
is FALSE, so it will not go fro further checks, then third record as well goes. Now, when fourth record comes for "Incepta", broker <> db_broker
evaluates to TRUE as broker has "Houlder insurance" and db_broker has "Incepta", so broker holds "Incepta" and yy = 0. Next, yy <> db_year
evaluates TRUE as yy = 0 and db_year has 2012. Next condition yy <> 0 is FALSE, so insert is not executed this time as well, so it as a whole skips "Houlder insurance" records completely.
Following is the corrected procedure
CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker TEXT;
DECLARE yy INT;
DECLARE jan INT;
DECLARE feb INT;
DECLARE mar INT;
DECLARE apr INT;
DECLARE may INT;
DECLARE jun INT;
DECLARE jul INT;
DECLARE aug INT;
DECLARE sep INT;
DECLARE toct INT;
DECLARE nov INT;
DECLARE tdec INT;
DECLARE db_broker TEXT;
DECLARE db_year INT;
DECLARE db_month INT;
DECLARE db_total INT;
DECLARE eof INT;
DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
, YEAR(submit_date_time) AS 'Year'
, MONTH(submit_date_time) AS 'Month'
, COUNT(*) AS 'Total'
FROM
survey_request a
JOIN survey d
ON d.src_reference = a.src_reference
JOIN contacts_companies b
ON b.reference = a.broker_company_reference
JOIN src_status c
ON c.reference = a.src_status_reference
WHERE
underwriter_company_reference = 73
GROUP BY
broker
, YEAR
, MONTH
ORDER BY
broker
, YEAR
, MONTH;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;
SET broker = '';
SET yy = 0;
SET jan = 0;
SET feb = 0;
SET mar = 0;
SET apr = 0;
SET may = 0;
SET jun = 0;
SET jul = 0;
SET aug = 0;
SET sep = 0;
SET toct = 0;
SET nov = 0;
SET tdec = 0;
TRUNCATE TABLE reportmonthsurveysubmitted;
OPEN cur1;
SET eof = 0;
WHILE eof = 0
DO
FETCH cur1 INTO db_broker, db_year, db_month, db_total;
IF broker <> db_broker THEN
IF yy <> 0 THEN
INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
END IF;
SET broker = db_broker;
SET yy = 0;
END IF;
IF yy <> db_year THEN
IF yy <> 0 THEN
INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
END IF;
SET yy = db_year;
SET jan = 0;
SET feb = 0;
SET mar = 0;
SET apr = 0;
SET may = 0;
SET jun = 0;
SET jul = 0;
SET aug = 0;
SET sep = 0;
SET toct = 0;
SET nov = 0;
SET tdec = 0;
END IF;
CASE db_month
WHEN 1 THEN
SET jan = db_total;
WHEN 2 THEN
SET feb = db_total;
WHEN 3 THEN
SET mar = db_total;
WHEN 4 THEN
SET apr = db_total;
WHEN 5 THEN
SET may = db_total;
WHEN 6 THEN
SET jun = db_total;
WHEN 7 THEN
SET jul = db_total;
WHEN 8 THEN
SET aug = db_total;
WHEN 9 THEN
SET sep = db_total;
WHEN 10 THEN
SET toct = db_total;
WHEN 11 THEN
SET nov = db_total;
WHEN 12 THEN
SET tdec = db_total;
END CASE;
END WHILE;
CLOSE cur1;
INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
END
Hope it will help you...
Upvotes: 1
Reputation: 29071
Try this:
CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker TEXT;
DECLARE yy INT;
DECLARE jan INT;
DECLARE feb INT;
DECLARE mar INT;
DECLARE apr INT;
DECLARE may INT;
DECLARE jun INT;
DECLARE jul INT;
DECLARE aug INT;
DECLARE sep INT;
DECLARE toct INT;
DECLARE nov INT;
DECLARE tdec INT;
DECLARE db_broker TEXT;
DECLARE db_year INT;
DECLARE db_month INT;
DECLARE db_total INT;
DECLARE eof INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
, YEAR(submit_date_time) AS 'Year'
, MONTH(submit_date_time) AS 'Month'
, COUNT(*) AS 'Total'
FROM
survey_request a
JOIN survey d
ON d.src_reference = a.src_reference
JOIN contacts_companies b
ON b.reference = a.broker_company_reference
JOIN src_status c
ON c.reference = a.src_status_reference
WHERE
underwriter_company_reference = 73
GROUP BY
broker
, YEAR
, MONTH
ORDER BY
broker
, YEAR
, MONTH;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;
SET broker = '';
SET yy = 0;
SET jan = 0;
SET feb = 0;
SET mar = 0;
SET apr = 0;
SET may = 0;
SET jun = 0;
SET jul = 0;
SET aug = 0;
SET sep = 0;
SET toct = 0;
SET nov = 0;
SET tdec = 0;
TRUNCATE TABLE reportmonthsurveysubmitted;
OPEN cur1;
curLoop:
LOOP
FETCH cur1 INTO db_broker, db_year, db_month, db_total;
IF eof = 1 THEN
CLOSE cur1;
LEAVE curLoop;
END IF;
IF broker <> db_broker THEN
SET broker = db_broker;
SET yy = 0;
END IF;
IF yy <> db_year THEN
IF yy <> 0 THEN
INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
END IF;
SET yy = db_year;
SET jan = 0;
SET feb = 0;
SET mar = 0;
SET apr = 0;
SET may = 0;
SET jun = 0;
SET jul = 0;
SET aug = 0;
SET sep = 0;
SET toct = 0;
SET nov = 0;
SET tdec = 0;
END IF;
CASE db_month
WHEN 1 THEN
SET jan = db_total;
WHEN 2 THEN
SET feb = db_total;
WHEN 3 THEN
SET mar = db_total;
WHEN 4 THEN
SET apr = db_total;
WHEN 5 THEN
SET may = db_total;
WHEN 6 THEN
SET jun = db_total;
WHEN 7 THEN
SET jul = db_total;
WHEN 8 THEN
SET aug = db_total;
WHEN 9 THEN
SET sep = db_total;
WHEN 10 THEN
SET toct = db_total;
WHEN 11 THEN
SET nov = db_total;
WHEN 12 THEN
SET tdec = db_total;
END CASE;
SET eof = 0;
END LOOP;
INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
END
Upvotes: 0