on Nov 14, 2016Version 7.0
I have a requirement to use a workflow variable in a sql select node to do some date comparison.
Here is the raw query that works on SQL Developer and also saves on the SQL Node.
select to_date('2016-11-12', 'yyyy-MM-dd') - to_date(sysdate) as req_day,
to_char(to_timestamp(replace('Mon Nov 14 14:06:00 ICT 2016', 'ICT '), 'DY MON DD HH24:MI:SS YYYY'), 'HH24MI') as appr_time
from dual
Here is the same query with variable substitutions and this does not save on the SQL Select Node. It throws an ORA exception.
select to_date('${jobUserData_FWDEFFECTIVESTARTDATE}', 'yyyy-MM-dd') - to_date(sysdate) as req_day,
to_char(to_timestamp(replace('${Job_lastUpdateDate}', 'ICT '), 'DY MON DD HH24:MI:SS YYYY'), 'HH24MI') as appr_time
from dual
I have tried the same in version 6.9.1 as well with no luck. I know for sure that this worked in older versions of Via.
Is there a way to disable this validation?
Try this, this worked for me in some cases:
Save your workflow with in the SQL node this statement:
select to_date('2016-11-12', 'yyyy-MM-dd') - to_date(sysdate) as req_day,
to_char(to_timestamp(replace('Mon Nov 14 14:06:00 ICT 2016', 'ICT '), 'DY MON DD HH24:MI:SS YYYY'), 'HH24MI') as appr_time
from dual
Export the workflow, unzip the zip file, find the sql query in one of the xml files and replace the '2016-11-12' with your variable and save the xml file. Rezip the workflow and import the workflow, the variable should now be working. Adjusting the query has to be done in the same manner.