I have a Control table where i store Source DB, Destination DB, Select query. Example
|CHBK||IVR||UTLWORK||select * from IVR.CHBK|
|CO_LOC||HRS||UTLWORK||SELECT * FROM HRS.CO_LOC WHERE CO_LOC_I IN (1, 105, 105, 188, 44, 127)|
Before extracting the data from source and loading into destination. I validate the SQL query in the column EXTR_SQL_T AND set the flag in control table. In next step, i will open a cursor and extract only records whose sql is valid and load the data accordingly.
FETCH Get_Table_Names INTO TABLES_NAME, SRC_SCH_NAME, DST_SCH_NAME, SQL_QUERY;
CALL DBC.SYSEXECSQL (:SQL_QUERY);
Now i get error message saying "5568 SQL statement is not supported within a stored procedure"
When i get the table list whose sqls are correct and try to use CALL DBC.SYSEXECSQL, it throws one more error.
FETCH Get_Final_List INTO TABLES_NAME_F, SRC_SCH_NAME_F, DST_SCH_NAME_F, SQL_QUERY_F;
SET Upd_Query='INSERT INTO '||SRC_SCH_NAME_F||'.'||TABLES_NAME_F|| SQL_QUERY_F||';';
CALL DBC.SYSEXECSQL (:Upd_Query);
Kindly provide the solution OR an alternative to achive my task.
What's the actual query in :SQL_QUERY?
A SELECT? You can't do a SELECT with sysexecsql. In Td13.10 you could probably OPEN/FETCH it.
And what's the error for problem 2?
sorry for late reply.
I got solution for problem 1 & 2. Now, i have new problem :)
i am getting error at:
CALL DBC.SYSEXECSQL ('UPDATE TDMUTLWORK.TD_CNTL SET SQL_VLD_STAT_C = "Y" WHERE TBL_N ='||:TABLES_NAME_F||';');
ERROR says: column Y not found in TDMUTLWORK.TD_CNTL. I hope syntax is correct!?
Double quoted strings are always object names, you need two single quotes instead:
"Y" -> ''Y''
What's the solution for problem 1?
How to use select in DBC.SYSEXECSQL()? Any other way to attain this? Please help !