AnsweredAssumed Answered

Cannot compile SQL Select node with to_date function

Question asked by Rahul Katariya Employee on Nov 14, 2016
Latest reply on Nov 15, 2016 by Rahul Katariya

Version 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?