Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » How to use datepart function in expression
How to use datepart function in expression [message #1070055] Wed, 17 July 2013 02:55
Christian Eugster is currently offline Christian Eugster
Messages: 134
Registered: July 2009
Location: St. Gallen Switzerland
Senior Member
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 02:59]

Report message to a moderator

Previous Topic:Changing from EL 2.1.3 to 2.5.0
Next Topic:EclipseLink-6069 when using BatchFetch and Inheritance
Goto Forum:
  


Current Time: Wed Aug 27 15:12:54 EDT 2014

Powered by FUDForum. Page generated in 0.01864 seconds