4 Replies Latest reply on Jul 30, 2015 2:21 PM by virtualdatabase

    Example of While Loop in procedure

    virtualdatabase Apprentice

      I have a need to union together 'n' number of tables.

      I have a table that contains table name and number of tables associated with it.

      I have built a proc that validates but it seems to run forever.

      I suspect I've done something wrong relating to the "WHILE"

       

      For now, I"m just trying to return the Variables.ExecString to show the query is built correctly..

       

      ANY HELP would be appreciated!!

       

      BEGIN

        DECLARE integer VARIABLES.NBR;

        DECLARE integer VARIABLES.counter = 0;

        DECLARE string VARIABLES.execString = '';

        DECLARE string VARIABLES.BaseexecString = '';

        VARIABLES.BaseexecString = 'Select  * FROM CLIENT_RNC_HISTORY';

        VARIABLES.NBR = (SELECT Util.TableCounts.NbrOfTables FROM Util.TableCounts WHERE nUtil.TableCounts.ConcernedTables = 'CLIENT_RNC_HISTORY');

        WHILE(VARIABLES.counter < VARIABLES.NBR)

        BEGIN

        VARIABLES.execString = ((((VARIABLES.BaseexecString || ' UNION ') || VARIABLES.BaseexecString) || '_') || VARIABLES.counter);

        END

        VARIABLES.counter = (VARIABLES.counter + 1);

        SELECT VARIABLES.execString AS ExecString;

      END

        • 1. Re: Example of While Loop in procedure
          Steven Hawkins Master

          The while statement takes effect of the next statement.  In this case that's a compound statement / block begin ... end - so the counter update is outside of the loop.

          • 2. Re: Example of While Loop in procedure
            virtualdatabase Apprentice

            Thanks that helped... now I'm only getting the 1st and last so it's not appending properly.. Thoughts?

            • 3. Re: Example of While Loop in procedure
              Steven Hawkins Master

              You are concatting baseexecstring twice.  You should be concatting execString:


              VARIABLES.BaseexecString = 'Select  * FROM CLIENT_RNC_HISTORY';

              VARIABLES.execString = 'Select  * FROM CLIENT_RNC_HISTORY';

              WHILE(VARIABLES.counter < VARIABLES.NBR)

              BEGIN

                VARIABLES.execString = ((((VARIABLES.execString || ' UNION ') || VARIABLES.BaseexecString) || '_') || VARIABLES.counter);

                VARIABLES.counter = (VARIABLES.counter + 1);

              END

               

              Also be aware of the default 4000 character limit with the string type in case this is a very long string.

              • 4. Re: Example of While Loop in procedure
                virtualdatabase Apprentice

                Thanks for the help, Steve!

                 

                Got it to work now it returns a string that **should** be able to be executed (it does work when copy/paste the result set from/to Squirrel)

                Few other 'rules' I needed to add...

                 

                BEGIN

                  DECLARE integer VARIABLES.NBR;

                  DECLARE integer VARIABLES.counter = 1;

                  DECLARE string VARIABLES.execString = '';

                  DECLARE string VARIABLES.LoopString = '';

                  DECLARE string VARIABLES.BaseexecString = '';

                  VARIABLES.BaseexecString = 'Select  *  FROM CLIENT_RNC_HISTORY';

                  VARIABLES.NBR = (SELECT Util.TableCounts.NbrOfTables FROM Util.TableCounts WHERE Util.TableCounts.ConcernedTables = 'CLIENT_RNC_HISTORY');

                  WHILE(VARIABLES.counter <= VARIABLES.NBR)

                  BEGIN

                  VARIABLES.LoopString = (((VARIABLES.BaseexecString || '_') || CASE WHEN VARIABLES.counter < 10 THEN ('0' || VARIABLES.counter) ELSE VARIABLES.counter END) || ' UNION ');

                  VARIABLES.execString = ((VARIABLES.execString || ' ') || VARIABLES.LoopString);

                  VARIABLES.counter = (VARIABLES.counter + 1);

                  END

                  VARIABLES.execString = substring(VARIABLES.execString, 1, (length(VARIABLES.execString) - 6));

                  SELECT VARIABLES.execString AS execstring;

                END