| Expression - dateDifference method usage [message #387775] |
Sat, 09 May 2009 00:43  |
 Kenneth Unpingco
Messages: 4
Registered: July 2009 |
Junior Member |
|
|
Hello,
I am trying to use the class:
org.eclipse.persistence.expressions.Expression by invoking the
dateDifference method:
public Expression dateDifference(java.lang.String datePart,
Expression comparisonExpression).
However, when I tried to use it I got the following error
exception:SybSQLException: 'MOD_DATE' is not a recognized datediff option.
All that I am trying to do is to get the total seconds elapsed between two
datetime columns (in my case, its the modDate column) from a table called
DocStore. I've made sure from the JavaDoc API that I've put in the
correct parameters.
I've used the following statement:
responseRequestDateTimeDifferenceExpression =
addrProcessExpression.get("requestDoc").get("modDate").dateDifference( "second",
addrProcessExpression.get("responseDoc").get("modDate"));
I expected the SQL transformation to get something like:
DATEDIFF(second, responseDoc.MOD_DATE, requestDoc.MOD_DATE)
but that did not happen. I ran across the datediff error exception. Shown
below are further details of the code and error exception.
What am I doing wrong? Any advice or ideas are truly appreciated.
Thank you,
Ken Unpingco
**************************
Here is the java code:
addrProcessExpression = new ExpressionBuilder();
reportQuery = new ReportQuery(AddressProcess.class, addrProcessExpression);
requestResponseDateRangeExpression =
addrProcessExpression.get("requestDoc").get("modDate").between(startDate,
endDate)
and(addrProcessExpression.get("responseDoc").get("modDate ").between(startDate,
endDate))
.and(addrProcessExpression.get("responseDoc").notEqual(null))
.and(addrProcessExpression.get("requestDoc").notEqual(null));
responseRequestDateTimeDifferenceExpression =
addrProcessExpression.get("requestDoc").get("modDate").dateDifference( "second",
addrProcessExpression.get("responseDoc").get("modDate"));
reportQuery.setShouldReturnSingleValue(true);
reportQuery.addSum("Total-seconds",
responseRequestDateTimeDifferenceExpression);
reportQuery.setSelectionCriteria(requestResponseDateRangeExp ression);
Query query = JpaHelper.createQuery(reportQuery,
(EntityManager)em.getDelegate());
******** Details for the log on the exception
********************************
Exception [EclipseLink-4002] (Eclipse Persistence Services -
2.0.0.v20090424-r4050):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sybase.jdbc3.jdbc.SybSQLException: 'MOD_DATE' is
not a recognized datediff option.
Error Code: 155
Call: SELECT SUM(DATEDIFF(t0.MOD_DATE, t3.MOD_DATE, t0.MOD_DATE)) FROM
DOCSTORE t3, ADDRESS_PROCESS t2, PROCESS t1, DOCSTORE t0 WHERE
((((((t0.MOD_DATE BETWEEN ? AND ?) AND (t3.MOD_DATE BETWEEN ? AND ?)) AND
NOT ((t2.RESPONSE_DOC_ID IS NULL))) AND NOT ((t2.REQUEST_DOC_ID IS NULL)))
AND ((t2.ID = t1.ID) AND (t1.discriminator = ?))) AND ((t0.ID =
t2.REQUEST_DOC_ID) AND (t3.ID = t2.RESPONSE_DOC_ID)))
bind => [2009-01-01 00:00:00.0, 2009-05-09 00:00:00.0, 2009-01-01
00:00:00.0, 2009-05-09 00:00:00.0, Address]
Query: ReportQuery(referenceClass=AddressProcess sql="SELECT
SUM(DATEDIFF(t0.MOD_DATE, t3.MOD_DATE, t0.MOD_DATE)) FROM DOCSTORE t3,
ADDRESS_PROCESS t2, PROCESS t1, DOCSTORE t0 WHERE ((((((t0.MOD_DATE
BETWEEN ? AND ?) AND (t3.MOD_DATE BETWEEN ? AND ?)) AND NOT
((t2.RESPONSE_DOC_ID IS NULL))) AND NOT ((t2.REQUEST_DOC_ID IS NULL))) AND
((t2.ID = t1.ID) AND (t1.discriminator = ?))) AND ((t0.ID =
t2.REQUEST_DOC_ID) AND (t3.ID = t2.RESPONSE_DOC_ID)))")
root cause
com.sybase.jdbc3.jdbc.SybSQLException: 'MOD_DATE' is not a recognized
datediff option.
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.03422 seconds
] 
Search
Help
Register
Login
Home
