Issue with to_char

[Dushyant Bhatia]

Hi Pavel,

Request if you could help with the below error. We are trying to use oraface and getting this error in one of our fucntions

19-02-2021 19:37:36,445 DEBUG [java.sql.PreparedStatement] (default task-23) {pstm-100144} 

PreparedStatement: SELECT BRANCH_CODE, UPPER(BRANCH_NAME) AS BRANCH_NAME, CURR_APPL_DT  FROM DAY_END WHERE BRANCH_CODE IN(         SELECT DISTINCT BRANCH_ID FROM WEEKLY_OFF WHERE WEEKLY_OFF='Y' AND STATUS !='DELETED' AND          (TO_CHAR(?, 'D') = WEEKLY_OFF1 OR TO_CHAR(?, 'D') = WEEKLY_OFF2)         UNION         SELECT DISTINCT BRANCH_ID FROM HOLIDAY_MASTER WHERE          AUTHORIZED_STATUS='AUTHORIZED' AND HOLIDAY_DT = ? AND STATUS != 'DELETED') AND BRANCH_CODE=? AND         CURR_APPL_DT = ? ORDER BY 1

19-02-2021 19:37:36,445 DEBUG [java.sql.PreparedStatement] (default task-23) {pstm-100144} Parameters: [2020-12-16 00:00:00.0, 2020-12-16 00:00:00.0, 2020-12-16 00:00:00.0, 8001, 2020-12-16 00:00:00.0]

19-02-2021 19:37:36,445 DEBUG [java.sql.PreparedStatement] (default task-23) {pstm-100144} Types: [java.sql.Timestamp, java.sql.Timestamp, java.sql.Timestamp, java.lang.String, java.sql.Timestamp]

19-02-2021 19:37:36,462 ERROR [com.ibatis.db.sqlmap.MappedStatement] (default task-23) Error executing 'getCheckHolidayBranch' in 'com/see/truetransact/transferobject/batchprocess/OperativeAccountBatchMap.xml'. Check the SQL statement. Cause: org.postgresql.util.PSQLException: ERROR: function to_char(unknown, unknown) is not unique

  Hint: Could not choose a best candidate function. You might need to add explicit type casts.

  Position: 213: org.postgresql.util.PSQLException: ERROR: function to_char(unknown, unknown) is not unique

  Hint: Could not choose a best candidate function. You might need to add explicit type casts.

  Position: 213

at com.ibatis.db.sqlmap.MappedStatement.runQueryForList(MappedStatement.java:469) [ibatis-db.jar:]

at com.ibatis.db.sqlmap.MappedStatement.executeQueryForList(MappedStatement.java:392) [ibatis-db.jar:]

at com.ibatis.db.sqlmap.MappedStatement.executeQueryForList(MappedStatement.java:383) [ibatis-db.jar:]

at com.ibatis.db.sqlmap.SqlMap.executeQueryForList(Unknown Source) [ibatis-db.jar:]

Thanks & Regards

Dushyant

[Pavel Stehule]

Hi

po 22. 2. 2021 v 4:33 odesílatel Dushyant Bhatia <dushy...@gmail.com> napsal:

you are try to use PostgreSQL buildin function to_char. This functions exists in two versions

│ pg_catalog │ to_char │ text             │ timestamp without time zone, text │ func │
│ pg_catalog │ to_char │ text             │ timestamp with time zone, text    │ func │

And in your query is not clean, what version should be used. You should to use explicit cast in this case

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orafce-genera...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/f1c6b1d8-f846-4d16-a74a-178aab53843an%40googlegroups.com.