tag:blogger.com,1999:blog-15319370.post112998373606928796..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Nesting MySQL Cursor Loopsrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger110125tag:blogger.com,1999:blog-15319370.post-89970857130792425812021-12-24T00:51:30.585+01:002021-12-24T00:51:30.585+01:00Thank you for the nested loop solution. I am star...Thank you for the nested loop solution. I am starting to learn procs and cursors. Your post helped me a lot :)blogerbashhttps://www.blogger.com/profile/14879280013775346958noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-13377440781939592312021-02-17T01:36:33.114+01:002021-02-17T01:36:33.114+01:00Thank you - yes that was the issue!
Here is the s...Thank you - yes that was the issue!<br /><br />Here is the sample data - I cannot do this in one query because in latest_nav table i need to search for the nav of the date exact match and if that is not found then i need to get the prior date for the scheme_id where there is an entry (so i need to do get nav from latest_nav where scheme_id = tblResults.scheme_id and latest_nav_date <<br />= tmp_Dates.Dates order by latest_nav.latest_nav_date limit 1<br /><br />tblResults<br />scheme_id<br />365<br />4644<br />1418<br /><br /><br />tmp_Dates<br />id Dates<br />1 2016-02-23<br />2 2016-03-01<br />3 2016-03-08<br /><br /><br />Latest_nav<br />scheme_id nav latest_nav_date<br />365 123 2016-02-23<br />365 124 2016-02-29<br />365 123 2016-03-08<br /><br /><br />Anonymoushttps://www.blogger.com/profile/09695196104604349524noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-37158072611196680682021-02-16T19:35:19.506+01:002021-02-16T19:35:19.506+01:00Hi @Unknown ,
thanks for the comment.
> what ...Hi @Unknown ,<br /><br />thanks for the comment.<br /><br />> what am i doing wrong here...i only get the first data set from the cursor and the loop<br /><br />I suppose you mean the outer loop only runs a single iteration?<br /><br />I think it's because of your loop condition:<br /><br />WHILE loop_ct < dt_ct DO<br /><br /><br />You initialize loop_ct and dt_ct both as 0, and then inside the loop, you increase loop_ct <br /><br />SET loop_ct = loop_ct + 1;<br /><br />So once that happens, loop_ct is 1 but dt_ct is still 0. So, the second time around, the loop condition becomes false.<br /><br />That said: I think you can probably rewrite all this logic to a single INSERT INTO ... SELECT statement. <br /><br />If you need some help with that, just post some sample data for all tables involved (like, 3 rows no more) and explain the situation before and expected situation after. <br /><br />Cheers, and best regards,<br />Roland.<br /><br />rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-91412499775211002752021-02-16T18:49:18.405+01:002021-02-16T18:49:18.405+01:00what am i doing wrong here...i only get the first ...what am i doing wrong here...i only get the first data set from the cursor and the loop<br /><br />DECLARE done INT DEFAULT FALSE;<br />DECLARE loopInitVal INT DEFAULT 0;<br />DECLARE dt_ct INT DEFAULT 0;<br />DECLARE loop_ct INT DEFAULT 0;<br /><br />DECLARE cur1 CURSOR FOR SELECT distinct scheme_id FROM tblResults; <br />DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE; <br /><br /><br />OPEN cur1;<br />FETCH cur1 INTO loopInitVal;<br />read_loop: LOOP<br /> <br /> IF done THEN<br /> LEAVE read_loop;<br /> END IF; <br /> <br /> <br /> WHILE loop_ct < dt_ct DO<br /> INSERT INTO tmp_scheme_nav_Dates (Dates, scheme_id, latest_nav) SELECT latest_nav_date, loopInitVal, latest_nav FROM latest_nav, tmp_Dates<br /> WHERE scheme_id = loopInitVal AND latest_nav_date <= Dates AND tmp_Dates.id = loop_ct + 1 ORDER BY nav_id DESC LIMIT 1;<br /> SET done = FALSE; <br /> SET loop_ct = loop_ct + 1;<br /> <br /> END WHILE;<br /> <br /> FETCH NEXT FROM cur1 INTO loopInitVal; <br /> END LOOP read_loop; <br /> CLOSE cur1;<br /> <br /> <br /><br />ENDAnonymoushttps://www.blogger.com/profile/09695196104604349524noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-32600336456137193462015-11-27T14:55:28.576+01:002015-11-27T14:55:28.576+01:00Thank you so much! Thank you so much! Honnikery Prabhakarhttps://www.blogger.com/profile/16893831786413907380noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-61321872652429716792015-11-27T14:53:55.949+01:002015-11-27T14:53:55.949+01:00It helped! thanks
It helped! thanks<br /><br /> Honnikery Prabhakarhttps://www.blogger.com/profile/16893831786413907380noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-60181683626569536202015-06-10T13:43:28.984+02:002015-06-10T13:43:28.984+02:00Thank you for the guide. It helped!Thank you for the guide. It helped!Kristjan Adojaanhttps://www.blogger.com/profile/01383926363511482561noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-11479190258925565732015-01-22T15:05:34.382+01:002015-01-22T15:05:34.382+01:00You are simply a genius! Thanks for this (more tha...You are simply a genius! Thanks for this (more than) useful infos! You save my day!!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-79108069570488997262013-10-28T08:39:56.978+01:002013-10-28T08:39:56.978+01:00Thank you so much! Helped a ton!Thank you so much! Helped a ton!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-11543198623457815012013-10-16T14:08:19.503+02:002013-10-16T14:08:19.503+02:00Grego,
no time. Go to stackoverflow or something...Grego, <br /><br />no time. Go to stackoverflow or something like that.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-18187553929718902642013-10-16T10:37:39.413+02:002013-10-16T10:37:39.413+02:00Hi Roland,
I have a nested cursor loop and I have...Hi Roland,<br /><br />I have a nested cursor loop and I have no idea what is wrong. When I run it then the mysql server goes down. Please check my SP...<br />Thanks in advance.<br /><br />DROP PROCEDURE IF EXISTS `get_scrap_list`;<br /><br />CREATE DEFINER = `root`@`localhost` PROCEDURE `get_scrap_list`()<br />BEGIN<br /><br /> DECLARE vintID, vintQTY INT DEFAULT 0;<br /> DECLARE vchrGEN_PN2 VARCHAR(255);<br /> DECLARE vendROW INT DEFAULT 0; <br /> <br /> DECLARE vcurROW CURSOR FOR<br /> SELECT<br /> ID<br /> FROM<br /> gen_pns<br /> WHERE<br /> ON_HAND > RGD<br /> ORDER BY<br /> GEN_PN;<br /> DECLARE CONTINUE HANDLER FOR NOT FOUND SET vendROW = 1; <br /><br />/* create temp table for results */<br />DROP TEMPORARY TABLE IF EXISTS temp1;<br /><br />CREATE TEMPORARY TABLE temp1 (<br /> PN VARCHAR(50) NOT NULL,<br /> GEN_PN VARCHAR(50) NOT NULL,<br /> ORG VARCHAR(50) NOT NULL,<br /> LOCATION VARCHAR(50) NOT NULL<br />); <br /><br /><br />OPEN vcurROW;<br /><br />LOOP1: LOOP<br /> <br /> FETCH vcurROW INTO vintID;<br /><br /> IF vendROW THEN <br /> CLOSE vcurROW;<br /> LEAVE LOOP1;<br /> END IF;<br /><br /> SELECT<br /> (ON_HAND - RGD), GEN_PN INTO vintQTY, vchrGEN_PN2<br /> FROM<br /> gen_pns<br /> WHERE<br /> ID = vintID<br /> LIMIT 1;<br /><br /> BLOCK2: BEGIN<br /><br /> DECLARE vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION VARCHAR(50);<br /> DECLARE vendROW2 INT DEFAULT 0;<br /><br /> DECLARE vcurROW2 CURSOR FOR<br /> SELECT<br /> rp.PN,<br /> rp.GEN_PN,<br /> rp.ORG,<br /> rp.LOCATION<br /> FROM<br /> rp,<br /> gen_pns<br /> WHERE<br /> gen_pns.GEN_PN = rp.GEN_PN<br /> AND gen_pns.GEN_PN = vchrGEN_PN2<br /> ORDER BY <br /> rp.PN<br /> LIMIT vintQTY;<br /> DECLARE CONTINUE HANDLER FOR NOT FOUND SET vendROW2 = 1;<br /><br /> OPEN vcurROW2;<br /> <br /> LOOP2: LOOP<br /><br /> FETCH vcurROW2 INTO vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION;<br /> <br /> IF vendROW2 THEN<br /> CLOSE vcurROW2;<br /> LEAVE LOOP2;<br /> END IF;<br /><br /> INSERT INTO temp1 (PN, GEN_PN, ORG, LOCATION) VALUES (vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION);<br /><br /> END LOOP LOOP2;<br /><br /> END BLOCK2;<br /><br />END LOOP LOOP1;<br /><br /><br />SELECT * FROM temp1;<br />DROP TEMPORARY TABLE temp1; <br /><br />END;<br /><br />Regards,<br />GergoAnonymoushttps://www.blogger.com/profile/06240014540543643967noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-42920235999234589422013-10-16T10:04:55.988+02:002013-10-16T10:04:55.988+02:00Hi Roland,
I have a nested cursor loop which does...Hi Roland,<br /><br />I have a nested cursor loop which doesn't work. This is in a SP. When I call it then the mysql server goes down. Could you help me what I am doing wrong? Thanks in advance.<br /><br />Here is the SP: <br /><br />DROP PROCEDURE IF EXISTS `get_scrap_list`;<br /><br />CREATE DEFINER = `root`@`localhost` PROCEDURE `get_scrap_list`()<br />BEGIN<br /><br /> DECLARE vintID, vintQTY INT DEFAULT 0;<br /> DECLARE vchrGEN_PN2 VARCHAR(255);<br /> DECLARE vendROW INT DEFAULT 0; <br /> <br /> DECLARE vcurROW CURSOR FOR<br /> SELECT<br /> ID<br /> FROM<br /> gen_pns<br /> WHERE<br /> ON_HAND > RGD<br /> ORDER BY<br /> GEN_PN;<br /> DECLARE CONTINUE HANDLER FOR NOT FOUND SET vendROW = 1; <br /><br />/* create temp table for results */<br />DROP TEMPORARY TABLE IF EXISTS temp1;<br /><br />CREATE TEMPORARY TABLE temp1 (<br /> PN VARCHAR(50) NOT NULL,<br /> GEN_PN VARCHAR(50) NOT NULL,<br /> ORG VARCHAR(50) NOT NULL,<br /> LOCATION VARCHAR(50) NOT NULL<br />); <br /><br /><br />OPEN vcurROW;<br /><br />LOOP1: LOOP<br /> <br /> FETCH vcurROW INTO vintID;<br /><br /> IF vendROW THEN <br /> CLOSE vcurROW;<br /> LEAVE LOOP1;<br /> END IF;<br /><br /> SELECT<br /> (ON_HAND - RGD), GEN_PN INTO vintQTY, vchrGEN_PN2<br /> FROM<br /> gen_pns<br /> WHERE<br /> ID = vintID<br /> LIMIT 1;<br /><br /> BLOCK2: BEGIN<br /><br /> DECLARE vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION VARCHAR(50);<br /> DECLARE vendROW2 INT DEFAULT 0;<br /><br /> DECLARE vcurROW2 CURSOR FOR<br /> SELECT<br /> rp.PN,<br /> rp.GEN_PN,<br /> rp.ORG,<br /> rp.LOCATION<br /> FROM<br /> rp,<br /> gen_pns<br /> WHERE<br /> gen_pns.GEN_PN = rp.GEN_PN<br /> AND gen_pns.GEN_PN = vchrGEN_PN2<br /> ORDER BY <br /> rp.PN<br /> LIMIT vintQTY;<br /> DECLARE CONTINUE HANDLER FOR NOT FOUND SET vendROW2 = 1;<br /><br /> OPEN vcurROW2;<br /> <br /> LOOP2: LOOP<br /><br /> FETCH vcurROW2 INTO vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION;<br /> <br /> IF vendROW2 THEN<br /> CLOSE vcurROW2;<br /> LEAVE LOOP2;<br /> END IF;<br /><br /> INSERT INTO temp1 (PN, GEN_PN, ORG, LOCATION) VALUES (vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION);<br /><br /> END LOOP LOOP2;<br /><br /> END BLOCK2;<br /><br />END LOOP LOOP1;<br /><br /><br />SELECT * FROM temp1;<br />DROP TEMPORARY TABLE temp1; <br /><br />END;<br /><br />Regards,<br />GergoAnonymoushttps://www.blogger.com/profile/06240014540543643967noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-61919958658900648472013-10-08T14:26:36.151+02:002013-10-08T14:26:36.151+02:00**** Great article ********* Great article *****Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-86722819060941282692013-09-13T15:35:58.124+02:002013-09-13T15:35:58.124+02:00@Dave,
thanks you! You were absolutely right. I ...@Dave, <br /><br />thanks you! You were absolutely right. I changed the occurrences of v_done to no_more_rows. <br /><br />It's baffling no-one caught this earlier, and great that you did. Thanks again!<br /><br />kind regards,<br /><br />Roland rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-69722331477384713562013-09-13T15:28:36.692+02:002013-09-13T15:28:36.692+02:00Typo: no_more_rows should be v_done?Typo: no_more_rows should be v_done?Dave Tapsonhttps://www.blogger.com/profile/10661171845277895466noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-63059004471033782732012-12-21T09:07:47.802+01:002012-12-21T09:07:47.802+01:00Thank's a lot, your Blog would be very usefull...Thank's a lot, your Blog would be very usefull. I'll use this post to teach my students as a very usefull and clear example.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-84884490678014464982012-08-23T10:00:37.401+02:002012-08-23T10:00:37.401+02:00nice article i found it very usefulnice article i found it very usefulAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-40858889385839825502012-08-23T09:59:25.157+02:002012-08-23T09:59:25.157+02:00Hey nice article.Hey nice article.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-17304725382265608562012-08-06T16:59:42.134+02:002012-08-06T16:59:42.134+02:00A very useful articleA very useful articleAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-56316118793796026482012-02-15T14:58:53.245+01:002012-02-15T14:58:53.245+01:00Gangadhar,
please take questions like this to a ...Gangadhar, <br /><br />please take questions like this to a public forum.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-25753927717673473332012-02-15T14:50:57.617+01:002012-02-15T14:50:57.617+01:00Hi Roland,
I have tried to write a procedure i ha...Hi Roland,<br /><br />I have tried to write a procedure i have spent almost 1 day but no luck, can you please check my code where am doing wrong.<br /><br /><br />DROP PROCEDURE IF EXISTS Insert_CallPut_C0;<br />//<br /><br />create procedure Insert_CallPut_C0()<br /><br />begin<br /> declare id int default 0;<br /> declare idVal bigint;<br /> declare EffectiveDate date;<br /> declare CallPutFlag varchar(1) default 'C';<br /> declare SecurityId bigint(20);<br /> declare ModifiedBatchID bigint(20) default 0;<br /> declare CreatedBatchID bigint(20) default 0;<br /> declare DeletedStamp varchar(40);<br /> declare CreatedStamp datetime;<br /> declare CreatedLoginName varchar(80);<br /> declare ModifiedStamp datetime;<br /> declare Price double;<br /> declare NextPrice double;<br /> declare ModifiedLoginName varchar(80);<br /> declare RecordType varchar(3) default 'C0';<br /> declare NextDate date;<br /> declare NoticePeriod varchar(5);<br /> declare Timing varchar(2);<br /> <br /> declare cur_sel CURSOR FOR select CASE WHEN calldet.CRDT is not null THEN calldet.CRDT WHEN calldet.CRDT is null THEN '1980-01-01' END, scx.SecurityId, scx.DeletedStamp, scx.CreatedStamp, scx.CreatedLoginName, scx.ModifiedStamp, calldet.CRPR, calldet.NXPR, scx.ModifiedLoginName, calldet.NXDT, calldet.CNOT, calldet.CTIM from sirs.SIRSSECCallPutDetail calldet, sirs.SIRSSECReference sirsref, sc_test.SCSECXref scx where calldet.ReferenceID=sirsref.ID and scx.SecurityId not in(select SecurityID from sc_test.SCSECCallPut where RecordType='C0') and scx.SecurityAlias=sirsref.USERNUM and scx.SecurityAliasType='CUSIP' limit 2;<br /><br />DECLARE CONTINUE HANDLER FOR NOT FOUND SET id=1;<br />begin<br /> select @max:= max(ID) from sc_test.SCSECCallPut;<br /> SET idVal = @max;<br />end;<br /><br />open cur_sel;<br />lbl: LOOP<br /> IF id=1 THEN<br /> LEAVE lbl;<br /> END IF;<br /> IF NOT id=1 THEN<br /> <br /> SET idVal = idVal+1;<br /> FETCH cur_sel INTO EffectiveDate,SecurityID,DeletedStamp,CreatedStamp,CreatedLoginName,ModifiedStamp,Price,NextPrice,ModifiedLoginName,NextDate,NoticePeriod,Timing;<br /> insert into scp4.test_callput(ID,EffectiveDate,CallPutFlag,SecurityID,ModifiedBatchID,CreatedBatchID,DeletedStamp,CreatedStamp,CreatedLoginName,ModifiedStamp,Price,NextPrice,ModifiedLoginName,RecordType,NextDate,NoticePeriod,Timing) values(idVal,EffectiveDate,CallPutFlag,SecurityID,ModifiedBatchID,CreatedBatchID,DeletedStamp,CreatedStamp,CreatedLoginName,ModifiedStamp,Price,NextPrice,ModifiedLoginName,RecordType,NextDate,NoticePeriod,Timing);<br /> END IF;<br /> END LOOP;<br /> CLOSE cur_sel;<br />END;<br />//<br /><br /><br />in my above procedure i have 1 cursor which select the columns from multiple tables,<br />then open the cursor, started the loop, in the loop am incrementing maxId value which is selected and stored in a variable idVal.<br />every thing is working fine like cursor fetching values and etc. but the thing is when i insert these values in to another table with above values from cursor and maxId value ie. idValue+1 then its always starting the idVal from 0 even though my maxId is returning 2000002.<br />can u please help in this.<br /><br />Thanks,<br />GangadharGangadharhttps://www.blogger.com/profile/04612836611035948173noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-40630157419434459542011-12-19T17:38:48.664+01:002011-12-19T17:38:48.664+01:00Andrew,
maybe you made a mistake somewhere? Chan...Andrew, <br /><br />maybe you made a mistake somewhere? Chances that your computer is the disobedient type seem pretty slim.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-24160814062920469382011-12-19T16:47:19.806+01:002011-12-19T16:47:19.806+01:00Nice article Roland, but I found that your second ...Nice article Roland, but I found that your second option just didn't work for my stored procedure on mySQL5.5. Setting v_done to false at the end of the inner loop didn't make any difference at all - instead it also caused the outer loop to terminate as it still saw v_done as true.Andrewnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-78790558052408373262011-12-16T11:48:21.856+01:002011-12-16T11:48:21.856+01:00Thanks a lot, dude! Your code helped me a lot, aft...Thanks a lot, dude! Your code helped me a lot, after many hours trying to deal with conditional loops.<br />For instance, did you knew that in a select for a cursor you can't use LIMIT a,b? :)<br />I trying to jump to the desired row using <br />select a,b from tbl limit i,1<br />where i was incremented, but i've got an error. Mysql accept "LIMIT a", but it is equivalent with "LIMIT 0,a", which is not what I was needed. :)<br /><br />Great article, as the one with the replacement of conditional loops with "LOOP".Tinel Barb (Romania)noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-27529632505426306532011-11-25T11:33:39.694+01:002011-11-25T11:33:39.694+01:00Hi Sriram R,
"Can you please please please l...Hi Sriram R,<br /><br />"Can you please please please look into this?"<br /><br />the problem is in the lines that read:<br /><br />SET @no_more_rows = FALSE;<br /><br />That should be:<br /><br />SET no_more_rows = FALSE;<br /><br />In your code, you're setting the user-defined variable @no_more_rows. But your loop condition reads from the local variable no_more_rows, and your handler also writes to that local variable. To make the outer loop continue, you must re-set that local variable. <br /><br />"My ulterior aim is to find the pairs of best matching rows."<br /><br />You should try to use a JOIN instead. But your code now does not compare rows at all, it simply loops through them.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.com