Hi all
I have a "view-entity" that include an aggregate field :
<alias entity-alias="IT" name="availableToPromiseTotal" function="sum" />
when i search in the view using "performFind" it give me an exception :
SELECT PR.PRODUCT_ID, PR.PRODUCT_CATEGORY_ID, PR.I_S_B_N10, PR.I_S_B_N13, PR.I_S_S_N, PR.I_S_OUT_OF_PRINT, PR.GOOGLE_ID, PR.EDITION, PR.AUTHOR, PR.PRODUCT_NAME, PR.COPYRIGHT_YEAR, PR.PAGES, PR.PRIMARY_PRODUCT_CATEGORY_ID, PR.MANUFACTURER_PARTY_ID, PR.RELEASE_DATE, PR.INTERNAL_NAME, PR.WEIGHT, PR.PRODUCT_STATUS_ID, PP.PRODUCT_PRICE_TYPE_ID, PP.PRICE, IT.FACILITY_ID, SUM(IT.QUANTITY_ON_HAND_TOTAL), SUM(IT.AVAILABLE_TO_PROMISE_TOTAL), PG.IS_MAIN_SUPPLIER FROM ((OFBIZ.PRODUCT PR INNER JOIN OFBIZ.PRODUCT_PRICE PP ON PR.PRODUCT_ID = PP.PRODUCT_ID) LEFT OUTER JOIN OFBIZ.INVENTORY_ITEM IT ON PR.PRODUCT_ID = IT.PRODUCT_ID) LEFT OUTER JOIN OFBIZ.PARTY_GROUP PG ON PR.MANUFACTURER_PARTY_ID = PG.PARTY_ID WHERE (SUM(IT.AVAILABLE_TO_PROMISE_TOTAL) >= ? AND IT.FACILITY_ID = ?) GROUP BY PR.PRODUCT_ID, PR.PRODUCT_CATEGORY_ID, PR.I_S_B_N10, PR.I_S_B_N13, PR.I_S_S_N, PR.I_S_OUT_OF_PRINT, PR.GOOGLE_ID, PR.EDITION, PR.AUTHOR, PR.PRODUCT_NAME, PR.COPYRIGHT_YEAR, PR.PAGES, PR.PRIMARY_PRODUCT_CATEGORY_ID, PR.MANUFACTURER_PARTY_ID, PR.RELEASE_DATE, PR.INTERNAL_NAME, PR.WEIGHT, PR.PRODUCT_STATUS_ID, PP.PRODUCT_PRICE_TYPE_ID, PP.PRICE, IT.FACILITY_ID, PG.IS_MAIN_SUPPLIER ORDER BY PR.PRODUCT_CATEGORY_ID ASC, PR.PRIMARY_PRODUCT_CATEGORY_ID ASC, PR.PRODUCT_ID ASC (Invalid use of an aggregate function.)
Simply the problem is in :WHERE (SUM(IT.AVAILABLE_TO_PROMISE_TOTAL) >= ? AND IT.FACILITY_ID = ?)
i don't know how to fix that .. i have to make the SUM function as a sub-query but i don't know how.
I hope that you can help me . Thanks in advance
Locked
