David Thanks for pointing this out. I watched every one of those very helpful dudes, but unfortunately, that was 4 months ago now.
Guess I better to watch them again now that I need the info. Skip -----Original Message----- From: David E Jones [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 9:15 PM To: [email protected] Subject: Re: Entity View Problem Yeah, all of them have to be in the group by list or have a function on them. If there are fields you don't want to have in the group by or have a function, then leave them out of the view-entity def or pass in a list of fields to select for the entity and leave the undesired fields out. BTW, this and hundreds of other similar tips are covered in the OFBiz framework training videos. -David On Nov 20, 2007, at 9:57 PM, Scott Gray wrote: > I'm no sql expert but isn't there some rule about the select fields > needing > to be in the group by? > > Regards > Scott > > On 21/11/2007, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> >> Anyone up late who can spot the problem with this: >> >> <view-entity entity-name="InvoiceAndItemTotal" >> package-name="com.opensourcestrategies.financials.invoice" >> title="Invoice and InvoiceItem Total"> >> <member-entity entity-alias="I" entity-name="Invoice"/> >> <member-entity entity-alias="II" entity-name="InvoiceItem"/> >> <alias entity-alias="I" name="partyId" /> >> <alias entity-alias="I" name="partyIdFrom" /> >> <alias entity-alias="I" name="statusId" /> >> <alias entity-alias="I" name="billingAccountId" /> >> <alias entity-alias="I" name="invoiceDate" /> >> <alias entity-alias="I" name="paidDate" /> >> <alias entity-alias="I" name="invoiceTypeId" /> >> <alias entity-alias="II" name="invoiceId" group-by="true" /> >> <alias entity-alias="II" name="orderTotal" function="sum"> >> <complex-alias operator="*"> >> <complex-alias-field entity-alias="II" field="quantity" >> default-value="1"/> >> <complex-alias-field entity-alias="II" field="amount" >> default-value="0"/> >> </complex-alias> >> </alias> >> <view-link entity-alias="II" rel-entity-alias="I"> >> <key-map field-name="invoiceId"/> >> </view-link> >> <relation type="one-nofk" rel-entity-name="InvoiceItem"> >> <key-map field-name="invoiceId"/> >> <key-map field-name="invoiceItemSeqId"/> >> </relation> >> </view-entity> >> >> Results in: >> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL >> Exception >> while executing the following: >> SELECT I.PARTY_ID, I.PARTY_ID_FROM, I.STATUS_ID, >> I.BILLING_ACCOUNT_ID, >> I.INVOICE_DATE, I.PAID_DATE, I.INVOICE_TYPE_ID, I.INVOICE_ID, >> SUM((COALESCE(II.QUANTITY,1) * COALESCE(II.AMOUNT,0))) FROM >> OFBIZ.INVOICE_ITEM II INNER JOIN OFBIZ.INVOICE I ON II.INVOICE_ID = >> I.INVOICE_ID GROUP BY I.INVOICE_ID >> >> Thanks in advance. >> >> Skip >> >>
