You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by "skip@thedevers" <sk...@thedevers.org> on 2007/11/21 05:04:37 UTC
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
RE: Entity View Problem
Posted by "skip@thedevers" <sk...@thedevers.org>.
David
Thanks for pointing this out. I watched every one of those very helpful
dudes, but unfortunately, that was 4 months ago now.
Guess I better to watch them again now that I need the info.
Skip
-----Original Message-----
From: David E Jones [mailto:jonesde@hotwaxmedia.com]
Sent: Tuesday, November 20, 2007 9:15 PM
To: user@ofbiz.apache.org
Subject: Re: Entity View Problem
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.
-David
On Nov 20, 2007, at 9:57 PM, Scott Gray wrote:
> 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, skip@thedevers <sk...@thedevers.org> 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
>>
>>
Re: Entity View Problem
Posted by David E Jones <jo...@hotwaxmedia.com>.
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.
-David
On Nov 20, 2007, at 9:57 PM, Scott Gray wrote:
> 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, skip@thedevers <sk...@thedevers.org> 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
>>
>>
RE: Entity View Problem
Posted by "skip@thedevers" <sk...@thedevers.org>.
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:lektran@gmail.com]
Sent: Tuesday, November 20, 2007 8:57 PM
To: user@ofbiz.apache.org
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, skip@thedevers <sk...@thedevers.org> 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
>
>
Re: Entity View Problem
Posted by Jacopo Cappellato <ti...@sastau.it>.
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
skip@thedevers 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:lektran@gmail.com]
> Sent: Tuesday, November 20, 2007 8:57 PM
> To: user@ofbiz.apache.org
> 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, skip@thedevers <sk...@thedevers.org> 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
>>
>>
RE: Entity View Problem
Posted by "skip@thedevers" <sk...@thedevers.org>.
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:lektran@gmail.com]
Sent: Tuesday, November 20, 2007 8:57 PM
To: user@ofbiz.apache.org
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, skip@thedevers <sk...@thedevers.org> 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
>
>
Re: Entity View Problem
Posted by Scott Gray <le...@gmail.com>.
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, skip@thedevers <sk...@thedevers.org> 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
>
>