Hi, using eclipselink 2.5.0 I need to query my db (ms sqlserver). The involved tables are position and receipt with a m : 1 relationship. Position has the attributes quantity and amount while receipt has an attribute "timestamp". I need to extract the sum(amount) and sum(quantity) and datepart("receipt.timestamp", "hour"), grouped by the attribute "hour" from a daterange (from to of receipt.timestamp). I am working with the eclipselink expressions
Expression expression = new ExpressionBuilder(Position.class);
Expression dateRangeExpression = new ExpressionBuilder().get("receipt").get("timestamp")
.between(dateRange[0], dateRange[1]);
final ReportQuery reportQuery = new ReportQuery(Position.class, expression);
reportQuery.addAttribute("hour", new ExpressionBuilder().get("receipt").get("timestamp").datePart("hour"));
reportQuery.addSum("quantity", Integer.class);
reportQuery.addSum("amount", Double.class);
reportQuery.addGrouping("hour");
executing the query results in the following exception:
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: 't0.re_timestamp' wird nicht als Option für datepart erkannt.
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: 't0.re_timestamp' wird nicht als Option für datepart erkannt.
Error Code: 155
Call: SELECT datepart(t0.re_timestamp, (?)), SUM(t1.po_quantity), SUM((FLOOR((((((t1.po_quantity * t1.po_price) * (? + t1.po_discount)) * (t1.po_fc_quotation / t0.re_dc_quotation)) / t0.re_dc_round_factor) + ?)) * t0.re_dc_round_factor)) FROM colibri_receipt t0, colibri_product_group t2, colibri_position t1 WHERE ((((t0.re_deleted = ?) AND (t2.pg_product_group_type = ?)) AND (t0.re_timestamp BETWEEN ? AND ?)) AND ((t0.re_id = t1.po_re_id) AND (t2.pg_id = t1.po_pg_id)))
bind => [7 parameters bound]
Query: ReportQuery(referenceClass=Position sql="SELECT datepart(t0.re_timestamp, (?)), SUM(t1.po_quantity), SUM((FLOOR((((((t1.po_quantity * t1.po_price) * (? + t1.po_discount)) * (t1.po_fc_quotation / t0.re_dc_quotation)) / t0.re_dc_round_factor) + ?)) * t0.re_dc_round_factor)) FROM colibri_receipt t0, colibri_product_group t2, colibri_position t1 WHERE ((((t0.re_deleted = ?) AND (t2.pg_product_group_type = ?)) AND (t0.re_timestamp BETWEEN ? AND ?)) AND ((t0.re_id = t1.po_re_id) AND (t2.pg_id = t1.po_pg_id)))")
at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:377)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:468)
at ch.eugster.colibri.persistence.queries.AbstractQuery.selectReportQueryResults(AbstractQuery.java:274)
at ch.eugster.colibri.persistence.queries.PositionQuery.selectDayHourStatisticsRange(PositionQuery.java:897)
at ch.eugster.colibri.report.time.views.TimeRangeView.selectItems(TimeRangeView.java:501)
at ch.eugster.colibri.report.time.views.TimeRangeView.createDataSource(TimeRangeView.java:513)
at ch.eugster.colibri.report.time.views.TimeRangeView$4.widgetSelected(TimeRangeView.java:291)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:240)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4165)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3754)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2701)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2665)
at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2499)
at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:679)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:668)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
at ch.eugster.colibri.report.app.ReportApplication.start(ReportApplication.java:47)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:344)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:622)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:577)
at org.eclipse.equinox.launcher.Main.run(Main.java:1410)
at org.eclipse.equinox.launcher.Main.main(Main.java:1386)
Can anyone tell me, how I have to setup the query to get the result? Thank you!
[Updated on: Wed, 17 July 2013 06:59]
Report message to a moderator
] 
Search
Help
Register
Login
Home
