Re: Entity View Problem

[David E Jones]

-David


On Nov 20, 2007, at 9:57 PM, Scott Gray wrote:

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.

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:

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

SELECT I.PARTY_ID, I.PARTY_ID_FROM, I.STATUS_ID, I.BILLING_ACCOUNT_ID,

smime.p7s
Description: S/MIME cryptographic signature