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 > >
