-
1. Re: Execute String without knowing columns
shawkins Jul 30, 2015 2:21 PM (in response to virtualdatabase)Only if returned the columns as an array.
-
2. Re: Execute String without knowing columns
virtualdatabase Jul 30, 2015 2:23 PM (in response to shawkins)Do you have an example ?
-
3. Re: Execute String without knowing columns
shawkins Jul 30, 2015 2:31 PM (in response to virtualdatabase)EXEC IMMEDIATE 'SELECT (1,2)' AS VAL INTEGER[];
-
4. Re: Execute String without knowing columns
virtualdatabase Aug 3, 2015 1:13 PM (in response to shawkins)In this case I assume it'd be a string[] but I don't see that option when defining the "column" of the result-set...
What am I missing.. should it be a clob?
-
5. Re: Execute String without knowing columns
rareddy Aug 3, 2015 5:05 PM (in response to virtualdatabase)I think it would be help if you can explain in little bit more detail as to what you are trying to do?
-
6. Re: Execute String without knowing columns
shawkins Aug 3, 2015 6:38 PM (in response to rareddy)Designer UI support for array types may be lacking. If you manually update the transformation it should validate. If not, then that's an issue.
-
7. Re: Execute String without knowing columns
virtualdatabase Aug 4, 2015 12:39 PM (in response to shawkins)the procedure validates but I cant create a resultset that uses the array type
-
8. Re: Execute String without knowing columns
virtualdatabase Aug 4, 2015 1:08 PM (in response to virtualdatabase)So I have come up with a proc that **should** be what I want but running into a new problem..
BEGIN
DECLARE integer VARIABLES.NBR;
DECLARE integer VARIABLES.counter = 1;
DECLARE string VARIABLES.execString = '';
DECLARE string VARIABLES.LoopString = '';
DECLARE string VARIABLES.ColumnsString = '';
DECLARE string VARIABLES.BaseexecString = '';
VARIABLES.BaseexecString = ('Select * FROM ' || Util.CLIENT_RNC_HISTORY_DYNAMIC.INPUT_TableName);
VARIABLES.NBR = (SELECT Util.TableCounts.NbrOfTables FROM Util.TableCounts WHERE Util.TableCounts.ConcernedTables = Util.CLIENT_RNC_HISTORY_DYNAMIC.INPUT_TableName);
VARIABLES.ColumnsString = (SELECT Util.POC_Columns.ColumnsString FROM Util.POC_Columns WHERE Util.POC_Columns.ConcernedTables = Util.CLIENT_RNC_HISTORY_DYNAMIC.INPUT_TableName);
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) - 6));
EXECUTE IMMEDIATE VARIABLES.execString AS VARIABLES.COlumnsString;
END
The Variables.Columns String is for example this: "CKEY string,
CDOCTYPE string,
CFLDCODE string,
CFLDVALUE string,
IROWNUM biginteger"
But I get the following error when validating:
ERROR: Error in parsing of sql - Invalid simple identifier format: [VARIABLES.COlumnsString]
Is this invocation of execute immediate with 2 variables NOT possible?
-
9. Re: Execute String without knowing columns
shawkins Aug 4, 2015 1:13 PM (in response to virtualdatabase)> EXECUTE IMMEDIATE VARIABLES.execString AS VARIABLES.COlumnsString;
No you cannot use an expression in the AS clause. It is for declaring the columns/types.
-
10. Re: Execute String without knowing columns
virtualdatabase Aug 4, 2015 2:00 PM (in response to shawkins)I know what comes after the " AS " and that is the contents of the variable VARIABLES.ColumnsString.
I assume you are stating that I cannot use an expression at all for the Execute Immediate except for the first part, the exec string.
-
11. Re: Execute String without knowing columns
shawkins Aug 4, 2015 2:21 PM (in response to virtualdatabase)> I know what comes after the " AS " and that is the contents of the variable VARIABLES.ColumnsString.
> I assume you are stating that I cannot use an expression at all for the Execute Immediate except for the first part, the exec string.
Only the sql in "execute immediate" is interpreted the rest needs to follow the expected syntax.