DynamicUnionsProc
virtualdatabase Aug 3, 2015 1:01 PMNot positve I can expect this but....
I have a proc
BEGIN
DECLARE integer VARIABLES.NBR;
DECLARE integer VARIABLES.counter = 1;
DECLARE string VARIABLES.execString = '';
DECLARE string VARIABLES.LoopString = '';
DECLARE string VARIABLES.BaseexecString = '';
DECLARE string VARIABLES.GetNumberQuery = '';
DECLARE string VARIABLES.ColumnsString = '';
DECLARE string VARIABLES.ColumnsQueryString = '';
VARIABLES.ColumnsQueryString = ((((((((((((('Select name' || '||') || ' ') || chr(39)) || ' ') || chr(39)) || '|| datatype ||') || chr(39)) || ',') || chr(39)) || ' from sys.columns where TableName = ') || Util.DynamicUnions.INPUT_TableName) || '_01') || Chr(39));
EXECUTE IMMEDIATE VARIABLES.ColumnsQueryString AS x string INTO #TEMP1;
VARIABLES.ColumnsString = (SELECT * FROM #TEMP1);
VARIABLES.ColumnsString = substring(VARIABLES.ColumnsString, 1, (Length(VARIABLES.ColumnsQueryString) - 1));
VARIABLES.BaseexecString = ('Select * FROM ' || Util.DynamicUnions.INPUT_TableName);
VARIABLES.GetNumberQuery = ('SELECT EmdeonUtil.TableCounts.NbrOfTables FROM Util.TableCounts WHERE Util.TableCounts.ConcernedTables = ' || Util.DynamicUnions.INPUT_TableName);
EXECUTE IMMEDIATE VARIABLES.GetNumberQuery AS y integer INTO #TEMP2;
VARIABLES.NBR = (SELECT * FROM #TEMP2);
WHILE(VARIABLES.counter <= VARIABLES.NBR)
BEGIN
VARIABLES.LoopString = (((VARIABLES.BaseExecString || '_') || CASE WHEN VARIABLES.counter < 10 THEN ('0' || VARIABLES.counter) ELSE VARIABLES.counter END) || ' UNION ALL ');
VARIABLES.execString = ((VARIABLES.execString || ' ') || VARIABLES.LoopString);
VARIABLES.counter = (VARIABLES.counter + 1);
END
VARIABLES.execString = substring(VARIABLES.execString, 1, (length(VARIABLES.execString) - 10));
SELECT ((VARIABLES.execString || ' as ') || VARIABLES.ColumnsString);
END
it compiles fine
At this point I'm just trying to return the ExecString that will later be replaced by an Execute Immediate VARIABLES.execString
My problem is upon execution I get this error:
Error: TEIID30168 Remote org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE VARIABLES.ColumnsQueryString AS x string INTO #TEMP1" with the SQL statement "VARIABLES.ColumnsQueryString" due to: TEIID31100 Parsing error: Encountered "TableName = CLIENT_RNC_HISTORY_01[*]'[*]" at line 1, column 94.
Lexical error. Character is not a valid token: '
SQLState: 50000
ErrorCode: 30168
I'm not sure what the issue is..
I've already made some changes using the TEMP tables above (of which I'm not sure I Need to)
Hopefully the idea of what I'm trying to do is clear...
Can I expect to be able to do it?
If so... what's wrong?
Thanks in advance!