将 Oracle 日期函数与 Spring 的 NamedParamenterJdbcTemplate 一起使用时出现问题

发布于 2024-11-17 09:28:18 字数 845 浏览 7 评论 0原文

我在尝试让在 SQL Developer（Oracle 免费数据库工具）中运行良好的 SQL 查询也可以使用 Spring 的 NamedParameterJdbcTemplate 类运行时遇到问题。

我的查询是：

String sql = " SELECT COUNT(*) FROM ( " +
                    " SELECT FE.USR_ID, MAX(FE.DATE_FIRST_SUB) AS SUB_DATE " +
                    " FROM FC, FE " +
                    " WHERE FC_STATUS = 'MEMBER' " +
                    " AND FC.FC_SPC_ID = :spcId " +
                    " AND FE.FE_USR_ID = FC.FC_USR_ID " +
                    " AND FE.DATE_FIRST_SUB IS NOT NULL " +
                    " GROUP BY FE_USR_ID " +
                 " ) " +
                 " WHERE SUB_DATE BETWEEN TO_DATE('01-JUN-2011', 'DD-MON-YYYY') AND TO_DATE('01-JUL-2011', 'DD-MON-YYYY') ";

它与我的日期有关，也许是格式？当我不在外部选择中使用 WHERE 子句时，它会起作用，当包含它时，从计数中返回 0 - 正如我提到的，运行 SQL 直接返回预期结果。

有什么建议吗？

谢谢。

原文

I'm having an issue trying to get my SQL query which works fine in SQL Developer (Oracles free database tool) to also work using Spring's NamedParameterJdbcTemplate class.

My query is:

String sql = " SELECT COUNT(*) FROM ( " +
                    " SELECT FE.USR_ID, MAX(FE.DATE_FIRST_SUB) AS SUB_DATE " +
                    " FROM FC, FE " +
                    " WHERE FC_STATUS = 'MEMBER' " +
                    " AND FC.FC_SPC_ID = :spcId " +
                    " AND FE.FE_USR_ID = FC.FC_USR_ID " +
                    " AND FE.DATE_FIRST_SUB IS NOT NULL " +
                    " GROUP BY FE_USR_ID " +
                 " ) " +
                 " WHERE SUB_DATE BETWEEN TO_DATE('01-JUN-2011', 'DD-MON-YYYY') AND TO_DATE('01-JUL-2011', 'DD-MON-YYYY') ";

It has something to do with my dates, formatting perhaps? When I don't use the WHERE clause in the outer select it works, when it's included 0 is returned from the count - as I mentioned running the SQL directly returns expected results.

Any advice?

Thanks.

分享到QQ

分享到微博