Example of While Loop in procedure

[virtualdatabase]

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

[shawkins]

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.

[virtualdatabase]

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

[shawkins]

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.

[virtualdatabase]

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