mySQL while exists with select data not recognozed in loop

Question

I have a stored procedure where I call a different stored procedure that creates a temp table and then use that temp table in a while exists loop. When I go to use the field names in the while loop, I am getting an error that one of the fields from the temp table doesn't exist when it does.

Below is the code:

   call GetProc1 (0, 1, 5, 111);
   while exists 
    (
        select GlobalMarketDesc, MarketFamilyName, Country, rank from tmpRanks;
    ) 
    do
        begin
            If GlobalMarketDesc = 'United States' then
                set strEventType = concat(GlobalMarketDesc, ' - ', MarketFamilyName);
            Else
                set strEventType = concat(GlobalMarketDesc, ' - ', Country, ' - ', MarketFamilyName);
            end if;
        end;
     end while;

The error Error Code: 1054 Unknown column 'GlobalMarketDesc' in 'field list'

When I changed to a cursor, I still get the above error. Here is my new code using a cursor:

    DECLARE no_more_records INT;

DECLARE cur_EventRanks CURSOR FOR
    select * from tmpEventRanks;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_records=1;
    set no_more_records = 0;
    open cur_EventRanks;

    cur_Loop: while (no_more_records=0)
    do
     begin
    -- Set up the Event Type field
    If GlobalMarketDesc = 'United States' then
       set strEventType = concat(GlobalMarketDesc, ' - ', MarketFamilyName);
    Else
       set strEventType = concat(GlobalMarketDesc, ' - ', Country, ' - ', MarketFamilyName);
    end if;
    end while cur_Loop;
    close cur_EventRanks;

Where do you expect `GlobalMarketDesc` to come from in `If GlobalMarketDesc = ...`? Nothing you've shown declares the variable, or changes it... and your loop is going to iterate either never or forever; `EXISTS` does not "pull a row" from a resultset. To iterate over results in MySQL you need a CURSOR. — Uueerdo, Mar 28 '18 at 20:54
The subroutine GetProc1 creates a temp table with the data. I was trying not to use a cursor since I know that it uses a lot of memory and my subroutine is complicated. If you know of any other way, please let me know — EddiRae, Mar 29 '18 at 00:39
I changed it to a cursor and I am still getting the same error. — EddiRae, Mar 29 '18 at 15:32
If you compare your code to example in the docs [here](https://dev.mysql.com/doc/refman/5.7/en/cursors.html); you'll notice you're missing any sort of `FETCH`. In my experience it is best to follow the general pattern established by the docs example. — Uueerdo, Mar 29 '18 at 15:49
I saw that ... I have corrected the code. I found the issue using the cursor. I need to declare all of the variables; which I am in the process of testing now. Thanks for your help. — EddiRae, Mar 29 '18 at 18:57

score 0 · Answer 1 · answered Apr 02 '18 at 15:00

I have corrected my code to include the cursor. Below is how I got it to work:

DECLARE cur_EventRanks CURSOR FOR
        select 
            *
        from tmpEventRanks;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_records=1;

    set no_more_records = 0;
    set cur_rec_cnt = 0;
    open cur_EventRanks;

    -- cur_Loop: while (no_more_records=0)
    cur_Loop: LOOP

        FETCH cur_EventRanks INTO 
            <== all of my fields ==>;

        if no_more_records = 1 then
            leave cur_Loop;
        end if;

        set cur_rec_cnt = cur_rec_cnt + 1;

         <== all of my processing needed ==>

END LOOP cur_Loop;

close cur_EventRanks;

The stored procedure didn't change.

mySQL while exists with select data not recognozed in loop

1 Answers1