I’m getting several errors in different places only on postgres (not on h2) with the ShipmentPackageSummary view entity.
In a custom service, that calls entity find on the ShipmentPackageSummary view entity
<entity-find entity-name="mantle.shipment.ShipmentPackageSummary" list="shipmentPackageSummaryList">
<econdition field-name="shipmentId"/>
<econdition field-name="productId"/>
<!-- <econdition field-name="orderId" from="labKitOrder.orderId"/>-->
<!-- <econdition field-name="orderPartSeqId" from="labKitOrder.orderPartSeqId"/>-->
<!-- <econdition field-name="returnId" operator="is-null"/>-->
<select-field field-name="shipmentPackageSeqId,shipmentRouteSegmentSeqId"/>
<order-by field-name="shipmentPackageSeqId"/>
</entity-find>
The current xml there does not have problems. However if you uncomment orderId, orderPartSeqId, or returnId, you get the following error:
o.moqui.i.c.MessageFacadeImpl ERROR: aggregate functions are not allowed in WHERE
Position: 717
Full Log
13:34:41.008 WARN 04126860-151 o.moqui.i.e.EntityFindImpl iteratorExtended: efb.finalSql: SELECT SPRS.SHIPMENT_PACKAGE_SEQ_ID, SRS.SHIPMENT_ROUTE_SEGMENT_SEQ_ID FROM (SHIPMENT_ROUTE_SEGMENT SRS INNER JOIN SHIPMENT SHMT ON SRS.SHIPMENT_ID = SHMT.SHIPMENT_ID LEFT OUTER JOIN SHIPMENT_PACKAGE_ROUTE_SEG SPRS ON SRS.SHIPMENT_ID = SPRS.SHIPMENT_ID AND SRS.SHIPMENT_ROUTE_SEGMENT_SEQ_ID = SPRS.SHIPMENT_ROUTE_SEGMENT_SEQ_ID LEFT OUTER JOIN LATERAL (SELECT FROM SHIPMENT_PACKAGE_CONTENT SPC WHERE SPRS.SHIPMENT_ID = SPC.SHIPMENT_ID AND SPRS.SHIPMENT_PACKAGE_SEQ_ID = SPC.SHIPMENT_PACKAGE_SEQ_ID AND SPC.PRODUCT_ID = ?) SPCT ON 1=1 LEFT OUTER JOIN LATERAL (SELECT FROM (SHIPMENT_ITEM_SOURCE SIS LEFT OUTER JOIN RETURN_HEADER RHDR ON SIS.RETURN_ID = RHDR.RETURN_ID) WHERE SRS.SHIPMENT_ID = SIS.SHIPMENT_ID AND MIN(RHDR.RETURN_ID) IS NULL) SMR ON 1=1 LEFT OUTER JOIN LATERAL (SELECT FROM (SHIPMENT_ITEM_SOURCE SIS LEFT OUTER JOIN ORDER_HEADER OHDR ON SIS.ORDER_ID = OHDR.ORDER_ID LEFT OUTER JOIN ORDER_ITEM OITM ON SIS.ORDER_ID = OITM.ORDER_ID AND SIS.ORDER_ITEM_SEQ_ID = OITM.ORDER_ITEM_SEQ_ID LEFT OUTER JOIN ORDER_PART OPRT ON OITM.ORDER_ID = OPRT.ORDER_ID AND OITM.ORDER_PART_SEQ_ID = OPRT.ORDER_PART_SEQ_ID) WHERE SRS.SHIPMENT_ID = SIS.SHIPMENT_ID AND (MIN(OHDR.ORDER_ID) = ? AND MIN(OPRT.ORDER_PART_SEQ_ID) = ?)) SMO ON 1=1) WHERE SHMT.SHIPMENT_ID = ? GROUP BY SPRS.SHIPMENT_PACKAGE_SEQ_ID, SRS.SHIPMENT_ROUTE_SEGMENT_SEQ_ID ORDER BY SPRS.SHIPMENT_PACKAGE_SEQ_ID ASC NULLS LAST
13:34:41.010 ERROR 04126860-151 o.moqui.i.a.XmlAction Error running groovy script (Error finding list of ShipmentPackageSummary by (shipmentId = 100618 (java.lang.String)) AND (productId = UM_LAB_KIT (java.lang.String)) AND (orderId = 101431 (java.lang.String)) AND (orderPartSeqId = 01 (java.lang.String)) AND (returnId IS NULL null) [42803]):
1 : import static org.moqui.util.ObjectUtilities.*
2 : import static org.moqui.util.CollectionUtilities.*
3 : import static org.moqui.util.StringUtilities.*
4 : import java.sql.Timestamp
5 : import java.sql.Time
6 : import java.time.*
7 : // these are in the context by default: ExecutionContext ec, Map<String, Object> context, Map<String, Object> result
8 : productId = ("""UM_LAB_KIT""")
9 : labKitOrders_xafind = ec.entity.find("mantle.order.OrderItemAndPartSummary").limit(1).orderBy("-placedDate,orderItemSeqId")
10 : .condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("customerPartyId", "equals", customerPartyId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("productId", "equals", productId, null, null, false, false, false, "false"))
11 : labKitOrders = labKitOrders_xafind.list()
12 : labKitOrder = (labKitOrders?.getFirst())
13 : if (!labKitOrder) {
14 : ec.message.addError(ec.resource.expand('''No lab kit orders found for this user''' ?: "Error in actions",''))
15 : return;
16 : }
17 :
18 : shipmentItemSourceList_xafind = ec.entity.find("mantle.shipment.ShipmentAndItemSource")
19 : .condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("orderId", "equals", labKitOrder.orderId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("orderItemSeqId", "equals", labKitOrder.orderItemSeqId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("shipmentStatusId", "not-in", shipmentStatusId, "ShipRejected,ShipCancelled", null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("statusId", "not-equals", statusId, "SisCancelled", null, false, false, false, "false"))
20 : shipmentItemSourceList = shipmentItemSourceList_xafind.list()
21 : shipmentItemSource = (shipmentItemSourceList?.getFirst())
22 : if (!shipmentItemSource) {
23 : ec.message.addError(ec.resource.expand('''Lab kit shipment not found''' ?: "Error in actions",''))
24 : return;
25 : }
26 :
27 : shipmentId = (shipmentItemSource.shipmentId)
28 : shipmentPackageSummaryList_xafind = ec.entity.find("mantle.shipment.ShipmentPackageSummary").selectField("shipmentPackageSeqId,shipmentRouteSegmentSeqId").orderBy("shipmentPackageSeqId")
29 : .condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("shipmentId", "equals", shipmentId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("productId", "equals", productId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("orderId", "equals", labKitOrder.orderId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("orderPartSeqId", "equals", labKitOrder.orderPartSeqId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("returnId", "is-null", returnId, null, null, false, false, false, "false"))
30 : shipmentPackageSummaryList = shipmentPackageSummaryList_xafind.list()
31 : shipmentPackageSummary = (shipmentPackageSummaryList?.getFirst())
32 : ec.logger.log("warn", """skip#LabKitOrderDeliverySuccessful: shipmentPackageSummaryList: ${shipmentPackageSummaryList}""", null)
33 : if (!shipmentPackageSummary) {
34 : ec.message.addError(ec.resource.expand('''Lab kit shipment package not found''' ?: "Error in actions",''))
35 : return;
36 : }
37 :
38 : if (true) {
39 : ec.service.sync().name("update#mantle.shipment.ShipmentPackageRouteSeg")
40 : .parameters([shipmentId:shipmentId, shipmentPackageSeqId:shipmentPackageSummary.shipmentPackageSeqId, shipmentRouteSegmentSeqId:shipmentPackageSummary.shipmentRouteSegmentSeqId, trackingStatusEnumId:'ShTsDelivered', trackingStatusDate:ec.user.nowTimestamp]).call()
41 :
42 : if (ec.message.hasError()) return
43 : }
44 : // make sure the last statement is not considered the return value
45 : return;
13:34:41.011 WARN 04126860-151 o.moqui.i.c.TransactionFacadeImpl Transaction rollback. The rollback was originally caused by: Error running service uniquely-me.UniquelyMeAppServices.skip#LabKitOrderDeliverySuccessful (Throwable)
org.moqui.impl.entity.EntitySqlException: Error finding list of ShipmentPackageSummary by (shipmentId = 100618 (java.lang.String)) AND (productId = UM_LAB_KIT (java.lang.String)) AND (orderId = 101431 (java.lang.String)) AND (orderPartSeqId = 01 (java.lang.String)) AND (returnId IS NULL null) [42803]
at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1157) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindBase.list(EntityFindBase.groovy:1003) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at uniquely_me_UniquelyMeAppServices_skip_LabKitOrderDeliverySuccessful.run(uniquely_me_UniquelyMeAppServices_skip_LabKitOrderDeliverySuccessful:30) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.runner.InlineServiceRunner.runService(InlineServiceRunner.java:59) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:322) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:11) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:988) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[javax.servlet-api-4.0.1.jar:4.0.1]
at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:110) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: aggregate functions are not allowed in WHERE
Position: 717
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118) ~[postgresql-jdbc4.jar:42.3.3]
at jdk.internal.reflect.GeneratedMethodAccessor72.invoke(Unknown Source) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:441) ~[postgresql-jdbc4.jar:42.3.3]
at com.sun.proxy.$Proxy30.executeQuery(Unknown Source) ~[?:?]
at bitronix.tm.resource.jdbc.proxy.PreparedStatementJavaProxy.executeQuery(PreparedStatementJavaProxy.java:102) ~[btm-3.0.0-20161020.jar:3.0.0-SNAPSHOT]
at org.moqui.impl.entity.EntityQueryBuilder.run(EntityQueryBuilder.java:109) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityQueryBuilder.executeQuery(EntityQueryBuilder.java:138) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindImpl.iteratorExtended(EntityFindImpl.java:152) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1155) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
... 20 more
13:34:41.012 WARN 04126860-151 o.moqui.i.c.TransactionFacadeImpl Transaction rollback for [Error running service uniquely-me.UniquelyMeAppServices.skip#LabKitOrderDeliverySuccessful (Throwable)]. Here is the current location:
org.moqui.BaseException: Rollback location
at org.moqui.impl.context.TransactionFacadeImpl.rollback(TransactionFacadeImpl.groovy:472) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.context.TransactionFacadeImpl.rollback(TransactionFacadeImpl.groovy:447) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:347) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:11) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:988) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[javax.servlet-api-4.0.1.jar:4.0.1]
at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:110) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
13:34:41.013 WARN 04126860-151 o.moqui.i.s.ServiceCallSyncImpl Error running service uniquely-me.UniquelyMeAppServices.skip#LabKitOrderDeliverySuccessful (Throwable) Artifact stack: uniquely-me.UniquelyMeAppServices.skip#LabKitOrderDeliverySuccessful, component://tools/screen/Tools/Service/ServiceRun.xml/run, component://tools/screen/Tools/Service/ServiceRun.xml, component://tools/screen/Tools/Service.xml, component://tools/screen/Tools.xml, component://webroot/screen/webroot/apps.xml, component://webroot/screen/webroot.xml
org.moqui.impl.entity.EntitySqlException: Error finding list of ShipmentPackageSummary by (shipmentId = 100618 (java.lang.String)) AND (productId = UM_LAB_KIT (java.lang.String)) AND (orderId = 101431 (java.lang.String)) AND (orderPartSeqId = 01 (java.lang.String)) AND (returnId IS NULL null) [42803]
at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1157) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindBase.list(EntityFindBase.groovy:1003) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at uniquely_me_UniquelyMeAppServices_skip_LabKitOrderDeliverySuccessful.run(uniquely_me_UniquelyMeAppServices_skip_LabKitOrderDeliverySuccessful:30) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.runner.InlineServiceRunner.runService(InlineServiceRunner.java:59) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:322) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:11) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:988) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[javax.servlet-api-4.0.1.jar:4.0.1]
at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:110) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: aggregate functions are not allowed in WHERE
Position: 717
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118) ~[postgresql-jdbc4.jar:42.3.3]
at jdk.internal.reflect.GeneratedMethodAccessor72.invoke(Unknown Source) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:441) ~[postgresql-jdbc4.jar:42.3.3]
at com.sun.proxy.$Proxy30.executeQuery(Unknown Source) ~[?:?]
at bitronix.tm.resource.jdbc.proxy.PreparedStatementJavaProxy.executeQuery(PreparedStatementJavaProxy.java:102) ~[btm-3.0.0-20161020.jar:3.0.0-SNAPSHOT]
at org.moqui.impl.entity.EntityQueryBuilder.run(EntityQueryBuilder.java:109) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityQueryBuilder.executeQuery(EntityQueryBuilder.java:138) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindImpl.iteratorExtended(EntityFindImpl.java:152) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1155) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
... 20 more
13:34:41.014 ERROR 04126860-151 o.moqui.i.c.MessageFacadeImpl Error finding list of ShipmentPackageSummary by (shipmentId = 100618 (java.lang.String)) AND (productId = UM_LAB_KIT (java.lang.String)) AND (orderId = 101431 (java.lang.String)) AND (orderPartSeqId = 01 (java.lang.String)) AND (returnId IS NULL null) [42803]
13:34:41.014 ERROR 04126860-151 o.moqui.i.c.MessageFacadeImpl ERROR: aggregate functions are not allowed in WHERE
Position: 717
In the example above, if you keep it the same, but comment out the select field so that all fields are queried, you get the error below. This also happens when you just go to the /qapps/tools/Entity/DataEdit/EntityDataFind?selectedEntity=mantle.shipment.ShipmentPackageSummary screen.
o.moqui.i.c.MessageFacadeImpl ERROR: missing FROM-clause entry for table "ohdr"
Position: 5793
Full Log
13:25:15.542 WARN 04126860-134 o.moqui.i.e.EntityFindImpl iteratorExtended: efb.finalSql: SELECT SBT.PSEUDO_ID, SBT.DESCRIPTION, SBT.WEIGHT_UOM_ID, SUM(SPRS.ESTIMATED_AMOUNT), SUM(SPRS.BASE_AMOUNT), SUM(SPRS.ACTUAL_AMOUNT), SUM(SPKG.WEIGHT), SPCT.PRODUCT_ID, SUM(SPCT.QUANTITY), COUNT(SPKG.SHIPMENT_PACKAGE_SEQ_ID), SUM(SOPQ.ORDER_PRODUCT_QUANTITY), SUM(SOSC.SHIPPING_CHARGE_TOTAL), SUM(SOSD.SHIPPING_DISCOUNT_TOTAL), SHMT.SHIPMENT_ID, SHMT.SHIPMENT_TYPE_ENUM_ID, SHMT.STATUS_ID, SHMT.FROM_PARTY_ID, SHMT.TO_PARTY_ID, SHMT.BIN_LOCATION_NUMBER, SHMT.PRODUCT_STORE_ID, SHMT.PRIORITY, SHMT.ENTRY_DATE, SHMT.SHIP_AFTER_DATE, SHMT.SHIP_BEFORE_DATE, SHMT.ESTIMATED_READY_DATE, SHMT.ESTIMATED_SHIP_DATE, SHMT.ESTIMATED_ARRIVAL_DATE, SHMT.LATEST_CANCEL_DATE, SHMT.PACKED_DATE, SHMT.PICK_CONTAINER_ID, SHMT.SHIP_WORK_EFFORT_ID, SHMT.RECEIVE_WORK_EFFORT_ID, SHMT.ASSEMBLY_WORK_EFFORT_ID, SHMT.ESTIMATED_SHIP_COST, SHMT.COST_UOM_ID, SHMT.ADDTL_SHIPPING_CHARGE, SHMT.ADDTL_SHIPPING_CHARGE_DESC, SHMT.SIGNATURE_REQUIRED_ENUM_ID, SHMT.HANDLING_INSTRUCTIONS, SHMT.OTHER_PARTY_ORDER_ID, SHMT.SYSTEM_MESSAGE_REMOTE_ID, SHMT.EXTERNAL_ID, SHMT.ORIGIN_ID, SRS.SHIPMENT_ROUTE_SEGMENT_SEQ_ID, SRS.DELIVERY_ID, SRS.SHIPPING_GATEWAY_CONFIG_ID, SRS.ORIGIN_FACILITY_ID, SRS.ORIGIN_POSTAL_CONTACT_MECH_ID, SRS.ORIGIN_TELECOM_CONTACT_MECH_ID, SRS.RETURN_POSTAL_CONTACT_MECH_ID, SRS.DESTINATION_FACILITY_ID, SRS.DEST_POSTAL_CONTACT_MECH_ID, SRS.DEST_TELECOM_CONTACT_MECH_ID, SRS.CARRIER_PARTY_ID, SRS.SHIPMENT_METHOD_ENUM_ID, SRS.TRADE_TERM_ENUM_ID, SRS.CUSTOMS_CERTIFY, SRS.CUSTOMS_CERTIFY_SIGNER, SRS.CUSTOMS_CONTENTS_ENUM_ID, SRS.CUSTOMS_NON_DELIVERY_ENUM_ID, SRS.CARRIER_DELIVERY_ZONE, SRS.CARRIER_RESTRICTION_CODES, SRS.CARRIER_RESTRICTION_DESC, SRS.BILLING_WEIGHT, SRS.BILLING_WEIGHT_UOM_ID, SRS.ACTUAL_TRANSPORT_COST, SRS.ACTUAL_SERVICE_COST, SRS.ACTUAL_OTHER_COST, SRS.ACTUAL_COST, SRS.ACTUAL_START_DATE, SRS.ACTUAL_ARRIVAL_DATE, SRS.ESTIMATED_START_DATE, SRS.MASTER_TRACKING_CODE, SRS.MASTER_TRACKING_URL, SRS.HOME_DELIVERY_TYPE, SRS.HOME_DELIVERY_DATE, SRS.THIRD_PARTY_ACCOUNT_NUMBER, SRS.THIRD_PARTY_POSTAL_CODE, SRS.THIRD_PARTY_COUNTRY_GEO_CODE, SRS.HIGH_VALUE_REPORT, SPRS.SHIPMENT_PACKAGE_SEQ_ID, SPRS.TRACKING_CODE, SPRS.TRACKING_URL, SPRS.TRACKING_STATUS_ENUM_ID, SPRS.TRACKING_SUB_STATUS, SPRS.TRACKING_STATUS_DATE, SPRS.TRACKING_ETA, SPRS.TRACKING_ORIG_ETA, SPRS.BOX_NUMBER, SPRS.LABEL_DATE, SPRS.LABEL_URL, SPRS.LABEL_IMAGE, SPRS.LABEL_INTL_SIGN_IMAGE, SPRS.LABEL_HTML, SPRS.LABEL_PRINTED, SPRS.INTERNATIONAL_INVOICE, SPRS.INTERNATIONAL_INVOICE_URL, SPRS.GATEWAY_STATUS, SPRS.GATEWAY_MESSAGE, SPRS.GATEWAY_LABEL_ID, SPRS.GATEWAY_RATE_ID, SPRS.GATEWAY_REFUND_ID, SPRS.GATEWAY_REFUND_STATUS, SPRS.RETURN_TRACKING_CODE, SPRS.RETURN_TRACKING_URL, SPRS.RETURN_TRACKING_STATUS_ENUM_ID, SPRS.RETURN_TRACKING_SUB_STATUS, SPRS.RETURN_TRACKING_STATUS_DATE, SPRS.RETURN_LABEL_DATE, SPRS.RETURN_LABEL_URL, SPRS.RETURN_LABEL_IMAGE, SPRS.RETURN_INTL_INVOICE_URL, SPRS.RETURN_GATEWAY_STATUS, SPRS.RETURN_GATEWAY_MESSAGE, SPRS.RETURN_GATEWAY_LABEL_ID, SPRS.RETURN_GATEWAY_RATE_ID, SPRS.RETURN_GATEWAY_REFUND_STATUS, SPRS.RETURN_ESTIMATED_AMOUNT, SPRS.RETURN_BASE_AMOUNT, SPRS.RETURN_ACTUAL_AMOUNT, SPRS.ESTIMATED_AMOUNT, SPRS.BASE_AMOUNT, SPRS.ACTUAL_AMOUNT, SPRS.PACKAGE_TRANSPORT_AMOUNT, SPRS.PACKAGE_SERVICE_AMOUNT, SPRS.PACKAGE_OTHER_AMOUNT, SPRS.COD_AMOUNT, SPRS.INSURANCE_AMOUNT, SPRS.INSURED_AMOUNT, SPKG.SHIPMENT_BOX_TYPE_ID, SPKG.WEIGHT, SPKG.WEIGHT_UOM_ID, SPKG.GATEWAY_PACKAGE_ID, SMO.ORDER_ID, SMO.ORDER_DISPLAY_ID, SMO.ORDER_EXTERNAL_ID, SMO.ORDER_ORIGIN_ID, SMO.ORDER_ENTRY_DATE, SMO.ORDER_PLACED_DATE, SMO.ORDER_PART_SEQ_ID, SMR.RETURN_ID, SMR.RETURN_DISPLAY_ID, SMR.RETURN_EXTERNAL_ID, SMR.RETURN_ORIGIN_ID, SMR.RETURN_ENTRY_DATE, SBT.DIMENSION_UOM_ID, SBT.BOX_LENGTH, SBT.BOX_WIDTH, SBT.BOX_HEIGHT, SBT.BOX_WEIGHT, SBT.DEFAULT_GROSS_WEIGHT, SBT.CAPACITY_UOM_ID, SBT.BOX_CAPACITY, SBT.GATEWAY_BOX_ID FROM (SHIPMENT_ROUTE_SEGMENT SRS INNER JOIN SHIPMENT SHMT ON SRS.SHIPMENT_ID = SHMT.SHIPMENT_ID LEFT OUTER JOIN SHIPMENT_PACKAGE_ROUTE_SEG SPRS ON SRS.SHIPMENT_ID = SPRS.SHIPMENT_ID AND SRS.SHIPMENT_ROUTE_SEGMENT_SEQ_ID = SPRS.SHIPMENT_ROUTE_SEGMENT_SEQ_ID LEFT OUTER JOIN SHIPMENT_PACKAGE SPKG ON SPRS.SHIPMENT_ID = SPKG.SHIPMENT_ID AND SPRS.SHIPMENT_PACKAGE_SEQ_ID = SPKG.SHIPMENT_PACKAGE_SEQ_ID LEFT OUTER JOIN SHIPMENT_BOX_TYPE SBT ON SPKG.SHIPMENT_BOX_TYPE_ID = SBT.SHIPMENT_BOX_TYPE_ID LEFT OUTER JOIN LATERAL (SELECT SPC.PRODUCT_ID AS PRODUCT_ID, SUM(SPC.QUANTITY) AS QUANTITY FROM SHIPMENT_PACKAGE_CONTENT SPC WHERE SPRS.SHIPMENT_ID = SPC.SHIPMENT_ID AND SPRS.SHIPMENT_PACKAGE_SEQ_ID = SPC.SHIPMENT_PACKAGE_SEQ_ID AND SPC.PRODUCT_ID = ? GROUP BY PRODUCT_ID) SPCT ON 1=1 LEFT OUTER JOIN LATERAL (SELECT MIN(RHDR.RETURN_ID) AS RETURN_ID, MIN(RHDR.DISPLAY_ID) AS RETURN_DISPLAY_ID, MIN(RHDR.EXTERNAL_ID) AS RETURN_EXTERNAL_ID, MIN(RHDR.ORIGIN_ID) AS RETURN_ORIGIN_ID, MIN(RHDR.ENTRY_DATE) AS RETURN_ENTRY_DATE FROM (SHIPMENT_ITEM_SOURCE SIS LEFT OUTER JOIN RETURN_HEADER RHDR ON SIS.RETURN_ID = RHDR.RETURN_ID) WHERE SRS.SHIPMENT_ID = SIS.SHIPMENT_ID) SMR ON 1=1 LEFT OUTER JOIN LATERAL (SELECT MIN(OHDR.ORDER_ID) AS ORDER_ID, MIN(OHDR.DISPLAY_ID) AS ORDER_DISPLAY_ID, MIN(OHDR.EXTERNAL_ID) AS ORDER_EXTERNAL_ID, MIN(OHDR.ORIGIN_ID) AS ORDER_ORIGIN_ID, MIN(OHDR.ENTRY_DATE) AS ORDER_ENTRY_DATE, MIN(OHDR.PLACED_DATE) AS ORDER_PLACED_DATE, MIN(OPRT.ORDER_PART_SEQ_ID) AS ORDER_PART_SEQ_ID FROM (SHIPMENT_ITEM_SOURCE SIS LEFT OUTER JOIN ORDER_HEADER OHDR ON SIS.ORDER_ID = OHDR.ORDER_ID LEFT OUTER JOIN ORDER_ITEM OITM ON SIS.ORDER_ID = OITM.ORDER_ID AND SIS.ORDER_ITEM_SEQ_ID = OITM.ORDER_ITEM_SEQ_ID LEFT OUTER JOIN ORDER_PART OPRT ON OITM.ORDER_ID = OPRT.ORDER_ID AND OITM.ORDER_PART_SEQ_ID = OPRT.ORDER_PART_SEQ_ID) WHERE SRS.SHIPMENT_ID = SIS.SHIPMENT_ID) SMO ON 1=1 LEFT OUTER JOIN LATERAL (SELECT SUM(OITM.QUANTITY) AS ORDER_PRODUCT_QUANTITY FROM ORDER_ITEM OITM WHERE SMO.MIN(OHDR.ORDER_ID) = OITM.ORDER_ID AND SMO.MIN(OPRT.ORDER_PART_SEQ_ID) = OITM.ORDER_PART_SEQ_ID AND OITM.ITEM_TYPE_ENUM_ID = ?) SOPQ ON 1=1 LEFT OUTER JOIN LATERAL (SELECT SUM(OITM.QUANTITY * OITM.UNIT_AMOUNT) AS SHIPPING_CHARGE_TOTAL FROM ORDER_ITEM OITM WHERE SMO.MIN(OHDR.ORDER_ID) = OITM.ORDER_ID AND SMO.MIN(OPRT.ORDER_PART_SEQ_ID) = OITM.ORDER_PART_SEQ_ID AND OITM.ITEM_TYPE_ENUM_ID = ?) SOSC ON 1=1 LEFT OUTER JOIN LATERAL (SELECT SUM(OITM.QUANTITY * OITM.UNIT_AMOUNT) AS SHIPPING_DISCOUNT_TOTAL FROM (ORDER_ITEM OITM INNER JOIN ORDER_ITEM PITM ON OITM.ORDER_ID = PITM.ORDER_ID AND OITM.PARENT_ITEM_SEQ_ID = PITM.ORDER_ITEM_SEQ_ID) WHERE SMO.MIN(OHDR.ORDER_ID) = OITM.ORDER_ID AND SMO.MIN(OPRT.ORDER_PART_SEQ_ID) = OITM.ORDER_PART_SEQ_ID AND (OITM.ITEM_TYPE_ENUM_ID IN (?, ?) AND PITM.ITEM_TYPE_ENUM_ID = ?)) SOSD ON 1=1) WHERE SHMT.SHIPMENT_ID = ? GROUP BY SBT.PSEUDO_ID, SBT.DESCRIPTION, SBT.WEIGHT_UOM_ID, SPCT.PRODUCT_ID, SHMT.SHIPMENT_ID, SHMT.SHIPMENT_TYPE_ENUM_ID, SHMT.STATUS_ID, SHMT.FROM_PARTY_ID, SHMT.TO_PARTY_ID, SHMT.BIN_LOCATION_NUMBER, SHMT.PRODUCT_STORE_ID, SHMT.PRIORITY, SHMT.ENTRY_DATE, SHMT.SHIP_AFTER_DATE, SHMT.SHIP_BEFORE_DATE, SHMT.ESTIMATED_READY_DATE, SHMT.ESTIMATED_SHIP_DATE, SHMT.ESTIMATED_ARRIVAL_DATE, SHMT.LATEST_CANCEL_DATE, SHMT.PACKED_DATE, SHMT.PICK_CONTAINER_ID, SHMT.SHIP_WORK_EFFORT_ID, SHMT.RECEIVE_WORK_EFFORT_ID, SHMT.ASSEMBLY_WORK_EFFORT_ID, SHMT.ESTIMATED_SHIP_COST, SHMT.COST_UOM_ID, SHMT.ADDTL_SHIPPING_CHARGE, SHMT.ADDTL_SHIPPING_CHARGE_DESC, SHMT.SIGNATURE_REQUIRED_ENUM_ID, SHMT.HANDLING_INSTRUCTIONS, SHMT.OTHER_PARTY_ORDER_ID, SHMT.SYSTEM_MESSAGE_REMOTE_ID, SHMT.EXTERNAL_ID, SHMT.ORIGIN_ID, SRS.SHIPMENT_ROUTE_SEGMENT_SEQ_ID, SRS.DELIVERY_ID, SRS.SHIPPING_GATEWAY_CONFIG_ID, SRS.ORIGIN_FACILITY_ID, SRS.ORIGIN_POSTAL_CONTACT_MECH_ID, SRS.ORIGIN_TELECOM_CONTACT_MECH_ID, SRS.RETURN_POSTAL_CONTACT_MECH_ID, SRS.DESTINATION_FACILITY_ID, SRS.DEST_POSTAL_CONTACT_MECH_ID, SRS.DEST_TELECOM_CONTACT_MECH_ID, SRS.CARRIER_PARTY_ID, SRS.SHIPMENT_METHOD_ENUM_ID, SRS.TRADE_TERM_ENUM_ID, SRS.CUSTOMS_CERTIFY, SRS.CUSTOMS_CERTIFY_SIGNER, SRS.CUSTOMS_CONTENTS_ENUM_ID, SRS.CUSTOMS_NON_DELIVERY_ENUM_ID, SRS.CARRIER_DELIVERY_ZONE, SRS.CARRIER_RESTRICTION_CODES, SRS.CARRIER_RESTRICTION_DESC, SRS.BILLING_WEIGHT, SRS.BILLING_WEIGHT_UOM_ID, SRS.ACTUAL_TRANSPORT_COST, SRS.ACTUAL_SERVICE_COST, SRS.ACTUAL_OTHER_COST, SRS.ACTUAL_COST, SRS.ACTUAL_START_DATE, SRS.ACTUAL_ARRIVAL_DATE, SRS.ESTIMATED_START_DATE, SRS.MASTER_TRACKING_CODE, SRS.MASTER_TRACKING_URL, SRS.HOME_DELIVERY_TYPE, SRS.HOME_DELIVERY_DATE, SRS.THIRD_PARTY_ACCOUNT_NUMBER, SRS.THIRD_PARTY_POSTAL_CODE, SRS.THIRD_PARTY_COUNTRY_GEO_CODE, SRS.HIGH_VALUE_REPORT, SPRS.SHIPMENT_PACKAGE_SEQ_ID, SPRS.TRACKING_CODE, SPRS.TRACKING_URL, SPRS.TRACKING_STATUS_ENUM_ID, SPRS.TRACKING_SUB_STATUS, SPRS.TRACKING_STATUS_DATE, SPRS.TRACKING_ETA, SPRS.TRACKING_ORIG_ETA, SPRS.BOX_NUMBER, SPRS.LABEL_DATE, SPRS.LABEL_URL, SPRS.LABEL_IMAGE, SPRS.LABEL_INTL_SIGN_IMAGE, SPRS.LABEL_HTML, SPRS.LABEL_PRINTED, SPRS.INTERNATIONAL_INVOICE, SPRS.INTERNATIONAL_INVOICE_URL, SPRS.GATEWAY_STATUS, SPRS.GATEWAY_MESSAGE, SPRS.GATEWAY_LABEL_ID, SPRS.GATEWAY_RATE_ID, SPRS.GATEWAY_REFUND_ID, SPRS.GATEWAY_REFUND_STATUS, SPRS.RETURN_TRACKING_CODE, SPRS.RETURN_TRACKING_URL, SPRS.RETURN_TRACKING_STATUS_ENUM_ID, SPRS.RETURN_TRACKING_SUB_STATUS, SPRS.RETURN_TRACKING_STATUS_DATE, SPRS.RETURN_LABEL_DATE, SPRS.RETURN_LABEL_URL, SPRS.RETURN_LABEL_IMAGE, SPRS.RETURN_INTL_INVOICE_URL, SPRS.RETURN_GATEWAY_STATUS, SPRS.RETURN_GATEWAY_MESSAGE, SPRS.RETURN_GATEWAY_LABEL_ID, SPRS.RETURN_GATEWAY_RATE_ID, SPRS.RETURN_GATEWAY_REFUND_STATUS, SPRS.RETURN_ESTIMATED_AMOUNT, SPRS.RETURN_BASE_AMOUNT, SPRS.RETURN_ACTUAL_AMOUNT, SPRS.ESTIMATED_AMOUNT, SPRS.BASE_AMOUNT, SPRS.ACTUAL_AMOUNT, SPRS.PACKAGE_TRANSPORT_AMOUNT, SPRS.PACKAGE_SERVICE_AMOUNT, SPRS.PACKAGE_OTHER_AMOUNT, SPRS.COD_AMOUNT, SPRS.INSURANCE_AMOUNT, SPRS.INSURED_AMOUNT, SPKG.SHIPMENT_BOX_TYPE_ID, SPKG.WEIGHT, SPKG.WEIGHT_UOM_ID, SPKG.GATEWAY_PACKAGE_ID, SMO.ORDER_ID, SMO.ORDER_DISPLAY_ID, SMO.ORDER_EXTERNAL_ID, SMO.ORDER_ORIGIN_ID, SMO.ORDER_ENTRY_DATE, SMO.ORDER_PLACED_DATE, SMO.ORDER_PART_SEQ_ID, SMR.RETURN_ID, SMR.RETURN_DISPLAY_ID, SMR.RETURN_EXTERNAL_ID, SMR.RETURN_ORIGIN_ID, SMR.RETURN_ENTRY_DATE, SBT.DIMENSION_UOM_ID, SBT.BOX_LENGTH, SBT.BOX_WIDTH, SBT.BOX_HEIGHT, SBT.BOX_WEIGHT, SBT.DEFAULT_GROSS_WEIGHT, SBT.CAPACITY_UOM_ID, SBT.BOX_CAPACITY, SBT.GATEWAY_BOX_ID ORDER BY SPRS.SHIPMENT_PACKAGE_SEQ_ID ASC NULLS LAST
13:25:15.544 ERROR 04126860-134 o.moqui.i.a.XmlAction Error running groovy script (Error finding list of ShipmentPackageSummary by (shipmentId = 100618 (java.lang.String)) AND (productId = UM_LAB_KIT (java.lang.String)) [42P01]):
1 : import static org.moqui.util.ObjectUtilities.*
2 : import static org.moqui.util.CollectionUtilities.*
3 : import static org.moqui.util.StringUtilities.*
4 : import java.sql.Timestamp
5 : import java.sql.Time
6 : import java.time.*
7 : // these are in the context by default: ExecutionContext ec, Map<String, Object> context, Map<String, Object> result
8 : productId = ("""UM_LAB_KIT""")
9 : labKitOrders_xafind = ec.entity.find("mantle.order.OrderItemAndPartSummary").limit(1).orderBy("-placedDate,orderItemSeqId")
10 : .condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("customerPartyId", "equals", customerPartyId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("productId", "equals", productId, null, null, false, false, false, "false"))
11 : labKitOrders = labKitOrders_xafind.list()
12 : labKitOrder = (labKitOrders?.getFirst())
13 : if (!labKitOrder) {
14 : ec.message.addError(ec.resource.expand('''No lab kit orders found for this user''' ?: "Error in actions",''))
15 : return;
16 : }
17 :
18 : shipmentItemSourceList_xafind = ec.entity.find("mantle.shipment.ShipmentAndItemSource")
19 : .condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("orderId", "equals", labKitOrder.orderId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("orderItemSeqId", "equals", labKitOrder.orderItemSeqId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("shipmentStatusId", "not-in", shipmentStatusId, "ShipRejected,ShipCancelled", null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("statusId", "not-equals", statusId, "SisCancelled", null, false, false, false, "false"))
20 : shipmentItemSourceList = shipmentItemSourceList_xafind.list()
21 : shipmentItemSource = (shipmentItemSourceList?.getFirst())
22 : if (!shipmentItemSource) {
23 : ec.message.addError(ec.resource.expand('''Lab kit shipment not found''' ?: "Error in actions",''))
24 : return;
25 : }
26 :
27 : shipmentId = (shipmentItemSource.shipmentId)
28 : shipmentPackageSummaryList_xafind = ec.entity.find("mantle.shipment.ShipmentPackageSummary").orderBy("shipmentPackageSeqId")
29 : .condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("shipmentId", "equals", shipmentId, null, null, false, false, false, "false")).condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("productId", "equals", productId, null, null, false, false, false, "false"))
30 : shipmentPackageSummaryList = shipmentPackageSummaryList_xafind.list()
31 : shipmentPackageSummary = (shipmentPackageSummaryList?.getFirst())
32 : ec.logger.log("warn", """skip#LabKitOrderDeliverySuccessful: shipmentPackageSummaryList: ${shipmentPackageSummaryList}""", null)
33 : if (!shipmentPackageSummary) {
34 : ec.message.addError(ec.resource.expand('''Lab kit shipment package not found''' ?: "Error in actions",''))
35 : return;
36 : }
37 :
38 : if (true) {
39 : ec.service.sync().name("update#mantle.shipment.ShipmentPackageRouteSeg")
40 : .parameters([shipmentId:shipmentId, shipmentPackageSeqId:shipmentPackageSummary.shipmentPackageSeqId, shipmentRouteSegmentSeqId:shipmentPackageSummary.shipmentRouteSegmentSeqId, trackingStatusEnumId:'ShTsDelivered', trackingStatusDate:ec.user.nowTimestamp]).call()
41 :
42 : if (ec.message.hasError()) return
43 : }
44 : // make sure the last statement is not considered the return value
45 : return;
13:25:15.544 WARN 04126860-134 o.moqui.i.c.TransactionFacadeImpl Transaction rollback. The rollback was originally caused by: Error running service uniquely-me.UniquelyMeAppServices.skip#LabKitOrderDeliverySuccessful (Throwable)
org.moqui.impl.entity.EntitySqlException: Error finding list of ShipmentPackageSummary by (shipmentId = 100618 (java.lang.String)) AND (productId = UM_LAB_KIT (java.lang.String)) [42P01]
at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1157) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindBase.list(EntityFindBase.groovy:1003) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at uniquely_me_UniquelyMeAppServices_skip_LabKitOrderDeliverySuccessful.run(uniquely_me_UniquelyMeAppServices_skip_LabKitOrderDeliverySuccessful:30) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.runner.InlineServiceRunner.runService(InlineServiceRunner.java:59) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:322) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:11) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:988) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[javax.servlet-api-4.0.1.jar:4.0.1]
at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:110) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "ohdr"
Position: 5793
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118) ~[postgresql-jdbc4.jar:42.3.3]
at jdk.internal.reflect.GeneratedMethodAccessor72.invoke(Unknown Source) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:441) ~[postgresql-jdbc4.jar:42.3.3]
at com.sun.proxy.$Proxy30.executeQuery(Unknown Source) ~[?:?]
at bitronix.tm.resource.jdbc.proxy.PreparedStatementJavaProxy.executeQuery(PreparedStatementJavaProxy.java:102) ~[btm-3.0.0-20161020.jar:3.0.0-SNAPSHOT]
at org.moqui.impl.entity.EntityQueryBuilder.run(EntityQueryBuilder.java:109) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityQueryBuilder.executeQuery(EntityQueryBuilder.java:138) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindImpl.iteratorExtended(EntityFindImpl.java:152) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1155) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
... 20 more
13:25:15.546 WARN 04126860-134 o.moqui.i.c.TransactionFacadeImpl Transaction rollback for [Error running service uniquely-me.UniquelyMeAppServices.skip#LabKitOrderDeliverySuccessful (Throwable)]. Here is the current location:
org.moqui.BaseException: Rollback location
at org.moqui.impl.context.TransactionFacadeImpl.rollback(TransactionFacadeImpl.groovy:472) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.context.TransactionFacadeImpl.rollback(TransactionFacadeImpl.groovy:447) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:347) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:11) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:988) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[javax.servlet-api-4.0.1.jar:4.0.1]
at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:110) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
13:25:15.547 WARN 04126860-134 o.moqui.i.s.ServiceCallSyncImpl Error running service uniquely-me.UniquelyMeAppServices.skip#LabKitOrderDeliverySuccessful (Throwable) Artifact stack: uniquely-me.UniquelyMeAppServices.skip#LabKitOrderDeliverySuccessful, component://tools/screen/Tools/Service/ServiceRun.xml/run, component://tools/screen/Tools/Service/ServiceRun.xml, component://tools/screen/Tools/Service.xml, component://tools/screen/Tools.xml, component://webroot/screen/webroot/apps.xml, component://webroot/screen/webroot.xml
org.moqui.impl.entity.EntitySqlException: Error finding list of ShipmentPackageSummary by (shipmentId = 100618 (java.lang.String)) AND (productId = UM_LAB_KIT (java.lang.String)) [42P01]
at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1157) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindBase.list(EntityFindBase.groovy:1003) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at uniquely_me_UniquelyMeAppServices_skip_LabKitOrderDeliverySuccessful.run(uniquely_me_UniquelyMeAppServices_skip_LabKitOrderDeliverySuccessful:30) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.runner.InlineServiceRunner.runService(InlineServiceRunner.java:59) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:322) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:11) ~[?:?]
at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:988) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[javax.servlet-api-4.0.1.jar:4.0.1]
at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:110) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "ohdr"
Position: 5793
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166) ~[postgresql-jdbc4.jar:42.3.3]
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118) ~[postgresql-jdbc4.jar:42.3.3]
at jdk.internal.reflect.GeneratedMethodAccessor72.invoke(Unknown Source) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:441) ~[postgresql-jdbc4.jar:42.3.3]
at com.sun.proxy.$Proxy30.executeQuery(Unknown Source) ~[?:?]
at bitronix.tm.resource.jdbc.proxy.PreparedStatementJavaProxy.executeQuery(PreparedStatementJavaProxy.java:102) ~[btm-3.0.0-20161020.jar:3.0.0-SNAPSHOT]
at org.moqui.impl.entity.EntityQueryBuilder.run(EntityQueryBuilder.java:109) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityQueryBuilder.executeQuery(EntityQueryBuilder.java:138) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindImpl.iteratorExtended(EntityFindImpl.java:152) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1155) ~[moqui-framework-3.1.0-rc1.jar:3.1.0-rc1]
... 20 more
13:25:15.548 ERROR 04126860-134 o.moqui.i.c.MessageFacadeImpl Error finding list of ShipmentPackageSummary by (shipmentId = 100618 (java.lang.String)) AND (productId = UM_LAB_KIT (java.lang.String)) [42P01]
13:25:15.548 ERROR 04126860-134 o.moqui.i.c.MessageFacadeImpl ERROR: missing FROM-clause entry for table "ohdr"
Position: 5793
I believe that the WHERE problem is because of the way postgres handles the WHERE clause where you can’t have any aggregate (min / max functions inside of it). It looks like the recommendation is to do a subquery.
This is a bit of an edge case when it comes to view entities that perform aggregations, but I’d imagine that there are other view entities that have the same problem on postgres.
Is there any thoughts on how to approach a solution for this? Do we need to rework the EntityQueryBuilder for this use case in postgres? Do you have any thoughts?