Skip,
you should include in the select statement only the fields that you want
to display, and all these fields must have the group-by attribute set
(or an aggregate function); for the remaining fields that you don't want
to display (or that are only used in the "where" clause) you don't need
to add the aggregate/group-by attribute.
Jacopo
[EMAIL PROTECTED] wrote:
Well, as we all know now, you must have either an aggregate function or
group-by clause unless your select statement does not reference the fields
in the view. So, I did this (note the use of the "max" function on the
string and date fields) just as a test:
<view-entity entity-name="InvoiceAndItemTotal"
package-name="org.ofbiz.accounting.ar"
title="Invoice and InvoiceItem Total">
<member-entity entity-alias="I" entity-name="Invoice"/>
<member-entity entity-alias="II" entity-name="InvoiceItem"/>
<alias entity-alias="II" name="invoiceId" group-by="true" />
<alias entity-alias="I" name="partyId" group-by="true"/>
<alias entity-alias="I" name="partyIdFrom" group-by="true"/>
<alias entity-alias="I" name="statusId" function="max" />
<alias entity-alias="I" name="billingAccountId" function="max"/>
<alias entity-alias="I" name="invoiceDate" function="max"/>
<alias entity-alias="I" name="paidDate" group-by="true"/>
<alias entity-alias="I" name="invoiceTypeId" function="max"/>
<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="I" rel-entity-alias="II">
<key-map field-name="invoiceId"/>
</view-link>
</view-entity>
This actually works in both derby and postgres and the raw sql works in
mysql (didnt actually try hooking it up, just ran a similiar test). Not
entirely sure of the impact on performance as this was a small data set.
Fun as this was, what I really want to do is just sum the InvoiceItem parts
and relate the results to Invoice in a single view.
Is there a way to do this? Can I create a view from a view-entity and
entity?
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
