Scott You are exactly right. Thats the problem. I have been thinking about this for half an hour now and can't figure out how to get around it in Ofbiz. In Ofbiz, I either have to apply a sum, avg, min, max, etc or set group-by="true". I expect putting in the extra group-by won't cause problems, but it just seems messy.
What I really want is function="pick the first you come across". I am no SQL expert either. Maybe there is one who wants to comment? Actually, as it turns out, you can have the field in the groupby clause or the where clause. Skip -----Original Message----- From: Scott Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 8:57 PM To: [email protected] Subject: Re: Entity View Problem 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 > >
