I'm using Iway 6.1 and simply attempting to insert a date into an Oracle table from an xml element. I have attempted a simple IFL expression in the User Properties of my SQL execution object...Here it is:

_IF(XPATH(//Date_Of_Birth != '',_concat('TO_DATE(',_qval(XPATH(//Date_of_Birth),'single'),',',_qval('MM/DD/YYYY','single'),')'),null)

(So if the date is not blank enter the string date as a picture formatted date else enter null)

When run, the SQL Statement errors. Looking at the trace I see this:

TO_DATE(''05/15/1974'',''MM/DD/YYYY'')

When it really should be this

TO_DATE('05/15/1974','MM/DD/YYYY')

I've tried various other approaches and none of them seem to work. am I using _qval incorrectly? I've tried without the 'single' keyword, also tried both char and action keywords together _qval(//xpath,'single','null') still everything places two single quotes together instead of one single quote. Can anyone help? Thanks.

This message has been edited. Last edited by: Phil DeBusk,

Hi Phil,

I consulted with the iWay team on this one. They created a test of the ifl function “_qval()” from the iSM command line using the tool “testfuncs”. It was tested in iSM 6.1.7.

A simple input file “Date.xml” was created with this as content:

06/16/1966

Will you please verify that your function returns the expected value - the date surrounded by single quotes?

If it doesn't, please open a case on InfoResponse Online.

Regards,

Kathryn

Thanks Kathryn, As it turns out the crux of my problem was in the transform configuration. I needed to set the "Text Qualifier" in the input to parse for double-quotes. This stripped all the messy quotes from my document and allowed all my functions to work as advertised.