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
  • Comment • 2

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?

Tim Willemstein
Tim Willemstein Nov 14, 2016 6:57 AM
Correct Answer

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.

See the reply in context
No one else had this question

Outcomes