Custom SQl

, ,
1

Hi,

I am Using CustomSql to convert GMT to unixTime.

Here is the following query:

SELECT (TO_DATE(STARTDATETIME,‘DD/MM/YYYY HH24:MI:SS’) - (TO_DATE(‘01/01/1970 00:00:00’,'DD/MM/YYYY HH24:MI:SS))) * 86400 FROM DUAL WHERE STARTDATETIME=?
Here STARTDATETIME datatype is varchar.

Excuting the service In Developer 6.5,i got the error message but its working fine in oracle.

The Error Message as follows:

Could not run ‘ConvertGMTToUNIX’.
com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service ConvertGMTToUNIX.
[ADA.1.316] Cannot execute the SQL statement "SELECT (TO_DATE(STARTDATETIME,‘DD/MM/YYYY HH24/MI/SS’)
- (TO_DATE(‘01/01/1970 00/00/00’,‘DD/MM/YYYY HH24/MI/SS’))) * 86400 FROM DUAL
WHERE STARTDATETIME = ?". "
COLOR=navy ORA-00904: “STARTDATETIME”: invalid identifier[/color]
"
ORA-00904: “STARTDATETIME”: invalid identifier

2

Since it is Oracle that is throwing the error I’m wondering how the same SQL worked in Oracle. Can you elaborate?

Also, I’m curious as to why you’re using the DB for this. Why not create a service on IS to convert a date object or string to millis?

3

A suggestion would be to change the TO_DATE and use TO_TIMESTAMP.

REF: [url]http://www.techonthenet.com/oracle/functions/to_timestamp.php[/url]

4

Hi,

We are using webMethods 7.1.2,We use to receive files throgh polling ports.While processing the files we are getting rror processing service: [FPL.0002.0004] Server Error:.

Can anybody tell resolution for this?

Thnaks a lot for your help.