DATE_FORMAT keyword is not working properly in Cross-connection editor.

Hi,

While trying to convert the date format from m/d/yyyy hh:mm:ss AM to yyyymmdd I am getting the below error in cross connection editor:

Got error 4208 '{0} ODBC data source returned an error: ORA-00904: “DATE_FORMAT”: invalid identifier
’ from HUB

Query used in cross connection editor :

SELECT DATE_FORMAT(A.BEGIN_DT, “%Y%m%d”) FROM O20P_PPE.OCDW.DISB_SCHEDULE_B A

here BEGIN_DT is in format :m/d/yyyy hh:mm:ss AM

This needs to be convert to yyyymmdd.

Please let me know how to resolve this issue.

Thanks,

Bharat

Anybody facing this issue? Any answers?

It depends on your Cross-Query where the date formatting occurs. Sometimes it is passed through to Oracle and sometimes it is done on the client. You would need to see the explain plan to determine how your particular query is being executed. But since your are getting an oracle error we know that the date formatting is being done by Oracle. So you need to used Oracle syntax.

SELECT TO_CHAR(CONTACT.BIRTH_DATE, ‘MM/DD/YYYY HH:MI:SS’)

Still its showing the same error. TO_CHAR is not supported in the Cross connection editor it seems. DATE_FORMAT is supported in cross connection editor. i have 2 DBs - Oracel and Greenplum. Not sure why DATE_FORMAT is not working properly.

Following error message is displayed while using TO_CHAR in cross connection editor:

Lookup Error

FUNCTION to_char does not exist. This function is not available for Cross-Connection queries. Please use an equivalent ANSI function.

I would like you to do two things:

  1. Provide me with the full SQL.

  2. Put the Cross-Query into a Cross-QUery SQL editor (as opposed to Query Builder) and add this hint in front of the SQL.

/diagnose_xquery/

Execute and there will be one field value. Copy and paste this return and give to me.

You can post both of these as attachments to this post.

Hi,

Please find attached the full Query.

Here OCDW is from Oracel and GP is from Greenplum.

Thanks,

Bharat
Full_Query.txt (2.12 KB)

Please find attached the output of Diagnore_Xquery output.

Thanks,

bharat
Diagnose_XQuery.txt (78.8 KB)

please let me know if any other information is needed.

Grr… this is really a topic of pain when it comes to functions in x-query.

You have to use MySQL functions or remote database functions depending on the query/sub-query is being pushed down or not, but the awkward part is that you don’t know the execution plan when the query is being written. Functions that’s being executed in remote database must be remote database specified functions; in any other case, it should be MySQL functions.

In your case, it should be TO_CHAR() in the first query because the query is pushed down and executed in remote data source, while it should be DATE_FORMAT() for the second query as it’s a Datahub query.

In release 2.5, we’re doing a much better job by transforming MySQL functions to remote database functions in pushed-down query. However for now, you must understand how to write functions according to the execution plan.