-
1. Re: Example of While Loop in procedure
shawkins Jul 30, 2015 1:12 PM (in response to virtualdatabase)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 Jul 30, 2015 1:20 PM (in response to shawkins)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
shawkins Jul 30, 2015 1:40 PM (in response to virtualdatabase)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 Jul 30, 2015 2:21 PM (in response to shawkins)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