Datastage routine for running SQL
Posted: Mon Oct 24, 2016 6:24 pm
I have created a datastage server routine to run SQLs by taking the below post as a reference:
viewtopic.php?p=351611
My routine is:
The problem which I am facing is that the quotes in the sql are being removed while writing to the file Query.sql.
Expected: SELECT TO_CHAR(TO_DATE('201608','YYYYMM'),'MON-YY') FROM DUAL;
Actual: SELECT TO_CHAR(TO_DATE(201608,YYYYMM),MON-YY) FROM DUAL;
I tried by wrapping the quotes with double quotes and backslash etc. It didn't work. Please help with any suggestions to get the same SQL written as it is.
viewtopic.php?p=351611
My routine is:
Code: Select all
*Write query to a temporary sql file.
cmd= "echo SELECT TO_CHAR(TO_DATE('201608','YYYYMM'),'MON-YY') FROM DUAL; > G:\temp\Datastage\Query.sql"
Call DSExecute("NT",cmd,Output,Retval)
*execute the sql file
cmd="sqlplus -s TEST1/test2@DSN< G:\temp\Datastage\Query.sql"
Call DSExecute("NT",cmd,Output,Returnval)
Ans=Output
Expected: SELECT TO_CHAR(TO_DATE('201608','YYYYMM'),'MON-YY') FROM DUAL;
Actual: SELECT TO_CHAR(TO_DATE(201608,YYYYMM),MON-YY) FROM DUAL;
I tried by wrapping the quotes with double quotes and backslash etc. It didn't work. Please help with any suggestions to get the same SQL written as it is.