RE: Entity View Problem

[clearchris]

Added to wiki per your request.

C

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 21, 2007 12:00 AM
To: clearchris
Subject: RE: Entity View Problem

clearchris

A very useful tip and it pointed out the problem instantly.

Thanks.

This in my view should go on the tips wiki page.

Skip

-----Original Message-----
From: clearchris [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 20, 2007 8:32 PM
To: [EMAIL PROTECTED]
Subject: RE: Entity View Problem


You can usually get a better error message by running the query directly on
the DB.

C

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 20, 2007 10:05 PM
To: user@ofbiz.apache.org
Subject: Entity View Problem

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