CODEWITHSUNDEEP
jdbceclipselink
feiwhoamI
feiwhoamI

Reputation: 25

Eclipselink generated SQL with ROWNUMBER, which is not recognized by SQLServer

I am using EclipseLink to connect different DBs. And run the same query against DB2 and SQLServer. The query runs well on DB2 but failed on SQLServer.

Here is the error message:

Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: 'ROWNUMBER'
is not a recognized built-in function name.
Error Code: 195

Call: SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_R
OWNM FROM (SELECT t0.EVAL_ID AS a1, t0.EVAL_TYP_ID AS a2, t0.CREATE_DATETM AS a3
, t0.EVAL_DESC AS a4, t0.EVAL_NAME AS a5, t0.REVISION_NBR AS a6, t0.UPDATE_DATET
M AS a7, t1.LOGICL_DB AS a8, t1.QUERY_STRING_HASH AS a9, t1.QUERY_STRING_TXT AS
a10 FROM EVAL t0, SQL_EVAL t1 WHERE ((t0.EVAL_NAME = ?) AND ((t1.EVAL_ID = t0.EV
AL_ID) AND (t0.EVAL_TYP_ID = ?))) ORDER BY t0.REVISION_NBR DESC) AS EL_TEMP) AS
EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?
        bind => [BA-TD.LODG_RM_NIGHT_TRANS_FACT.PRICE_AMT_LOCAL_WITH_OMS, 1, 1,
0]

I think this query is generated by EclipseLink since my original query is very simple. My question is, SQLServer cannot recognize 'ROWNUMBER' so how to change it to 'ROW_NUMBER'? Is there any way to config how EclipseLink does the SQL generation?

Thanks!

Upvotes: 0

Views: 473

Answers (1)

Petros Splinakis
Petros Splinakis

Reputation: 603

Try setting the eclipselink.target-database property to org.eclipse.persistence.platform.database.SQLServerPlatform:

<property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.SQLServerPlatform"/>

Upvotes: 1

Related Questions