Datastage routine for running SQL

[chandu123]

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:

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

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.

[chulett]

Try putting each single quote inside a pair of single quotes, i.e. each one becomes three.

[chulett]

I'm hoping your SQL example is just a test as you certainly don't need SQL or a database to get that string into MON-YY format.