You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by snowc <ch...@gmail.com> on 2009/08/17 19:26:32 UTC
entity condition expression problem
I have some legacy data that I am trying to access in ofbiz.
<entity name="E1">
<field name="e1ID"/>
<field name="actualNetAmount"/>
<field name="estimatedNetAmount"/>
</entity>
Conceptually, I want to filter as shown here:
SELECT e1Id, actualNetAmount, estimatedNetAmount
FROM E1 WHERE actualNetAmount > estimatedNetAmount
<entity-condition entity="E1">
<condition-expr field="actualNetAmount" operator="greater"
value="${this.estimatedNetAmount}"/>
</entity-condition>
How can I achieve the equivalent of "this" is a entity condition expression?
I do not want to filter by iterating the list as the data volumes are huge.
One option I am thinking of is to create a SQL view and use that to filter
the data:
SELECT
e1Id, actualNetAmount, estimatedNetAmount,
CASE
WHEN actualNetAmount > estimatedNetAmount THEN 1
ELSE 0
END CASE AS actualGTEstimated
FROM E1
Maybe I could use DynamicViewEntities, but I was trying to use less
beanshell/java and more minilang.
Many thanks in advance, Chris
--
View this message in context: http://www.nabble.com/entity-condition-expression-problem-tp25010865p25010865.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: entity condition expression problem
Posted by Bob Morley <rm...@emforium.com>.
Hi Chris,
At our company we make very heavy use of view-entities; but in order to do
so we have made quite a few enhancements that have yet to be bundled up and
offered back to the community. The one thing that hit me was (I believe)
Ofbiz OOTB does not support the notion of having a v-e based on another v-e
(this is something we make a lot of use of). We have made so many changes I
often have to do some research to be sure; but I remember fixing a bug in
the v-e population based on the fact that the v-e was not based solely on
entities.
With this support you could create your aggr. v-e and then have a v-e that
will use it joined with your entity using the technique we talked about
earlier.
Another "hack" we put in was the notion of being able to specify
"qualifier-sql" in the v-e definition. Effectively you could write a piece
of sql right in the v-e, so it could absolutely do the where clause you were
looking for. Now I think we would have to formalize the grammar of such an
enhancement.
I think the approach you are taking is fine. It is good to hear that other
folks have struggled with a few of the limitations of view-entities, and I
will look towards trying to bundle/formalize some of this support to try to
get it in the product moving forward.
Take care,
Bob
snowc wrote:
>
> So close, yet so far...
>
> I simplified my problem description. Actually, the complex-alias needed
> was:
>
> <alias name="amountDue">
> <complex-alias operator="-">
> <complex-alias-field entity-alias="SalvageAmounts"
> field="calculatedSalvageGross"/>
> <complex-alias-field entity-alias="VehiclePaymentSummaryView"
> field="vehiclePaymentTotalAmount"/>
> </complex-alias>
> </alias>
>
> Where:
>
> - SalvageAmounts is an entity that sits on top of a database view, not a
> table.
> - VehiclePaymentSummaryView is a view-entity with an aggregate function.
>
> Running the report results in the error:
>
> ---- exception report
> ----------------------------------------------------------
>
> Failure in by condition find operation, rolling back transaction
> Exception: org.ofbiz.entity.GenericDataSourceException
> Message: SQL Exception while executing the following:SELECT
> Uplift.ingUplNumber,
> Uplift.strUplInsurer, Uplift.dtmUplPickupDate,
> VehiclePaymentSummaryView.SUM_Ve
> hiclePayment_ingAmount_, UpliftFlags.strFlag, UpliftFlags.blnAgreed,
> UpliftFlags
> .blnReviewed, SalvageAmounts.upliftPAVBand, SalvageAmounts.ingPercentage,
> Salvag
> eAmounts.calculatedSalvageNet, SalvageAmounts.calculatedSalvageVat,
> SalvageAmoun
> ts.calculatedSalvageGross, (SalvageAmounts.calculatedSalvageGross -
> VehiclePayme
> ntSummaryView.SUM(VehiclePayment.ingAmount)) FROM ((dbo.tblUplift Uplift
> LEFT OU
> TER JOIN (SELECT Uplift.ingUplNumber AS Uplift_ingUplNumber,
> SUM(VehiclePayment.
> ingAmount) AS SUM_VehiclePayment_ingAmount_ FROM dbo.tblUplift Uplift LEFT
> OUTER
> JOIN dbo.tblVehiclePayment VehiclePayment ON Uplift.ingUplNumber =
> VehiclePayme
> nt.ingUpliftNumber GROUP BY Uplift.ingUplNumber) VehiclePaymentSummaryView
> ON Up
> lift.ingUplNumber = VehiclePaymentSummaryView.Uplift_ingUplNumber) INNER
> JOIN db
> o.tblUpliftFlags UpliftFlags ON Uplift.ingUplNumber =
> UpliftFlags.ingUplift) INN
> ER JOIN dbo.vSalvageAmounts SalvageAmounts ON Uplift.ingUplNumber =
> SalvageAmoun
> ts.ingUplNumber WHERE (Uplift.ingUplNumber > ? AND UpliftFlags.strFlag = ?
> AND U
> pliftFlags.blnAgreed = ? AND UpliftFlags.blnReviewed = ? AND
> Uplift.dtmUplPickup
> Date IS NOT NULL) (Cannot find either column "VehiclePaymentSummaryView"
> or the
> user-defined function or aggregate "VehiclePaymentSummaryView.SUM", or the
> name
> is ambiguous.)
>
> --
>
> I think I will push my logic back into a database view. I find that using
> pure entity and view-entity does not have the power needed to work with
> some legacy data designs. For example other posts have mentioned wanting
> to specify join conditions in view-link's which would be very useful.
>
> Many thanks, Chris
>
>
>
> Bob Morley wrote:
>>
>> I think I would create a view-entity that aliased all of the fields you
>> have here and added a complex-alias for the difference of the actual and
>> estimated net amounts. Something like --
>>
>> <alias name="actualEstimatedDelta">
>> <complex-alias operator="-">
>> <complex-alias-field entity-alias="E1"
>> field="actualNetAmount"/>
>> <complex-alias-field entity-alias="E1"
>> field="estimatedNetAmount"/>
>> </complex-alias>
>> </alias>
>>
>> Then do your entity condition on this field in your v-e.
>>
>> <condition-expr field="actualEstimatedDelta" operator="greater"
>> value="0"/>
>>
>> Bob
>>
>>
>> snowc wrote:
>>>
>>> I have some legacy data that I am trying to access in ofbiz.
>>>
>>> <entity name="E1">
>>> <field name="e1ID"/>
>>> <field name="actualNetAmount"/>
>>> <field name="estimatedNetAmount"/>
>>> </entity>
>>>
>>> Conceptually, I want to filter as shown here:
>>>
>>> SELECT e1Id, actualNetAmount, estimatedNetAmount
>>> FROM E1 WHERE actualNetAmount > estimatedNetAmount
>>>
>>> <entity-condition entity="E1">
>>> <condition-expr field="actualNetAmount" operator="greater"
>>> value="${this.estimatedNetAmount}"/>
>>> </entity-condition>
>>>
>>> How can I achieve the equivalent of "this" is a entity condition
>>> expression?
>>>
>>> I do not want to filter by iterating the list as the data volumes are
>>> huge.
>>>
>>> One option I am thinking of is to create a SQL view and use that to
>>> filter the data:
>>>
>>> SELECT
>>> e1Id, actualNetAmount, estimatedNetAmount,
>>> CASE
>>> WHEN actualNetAmount > estimatedNetAmount THEN 1
>>> ELSE 0
>>> END CASE AS actualGTEstimated
>>> FROM E1
>>>
>>> Maybe I could use DynamicViewEntities, but I was trying to use less
>>> beanshell/java and more minilang.
>>>
>>> Many thanks in advance, Chris
>>>
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/entity-condition-expression-problem-tp25010865p25038183.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: entity condition expression problem
Posted by snowc <ch...@gmail.com>.
So close, yet so far...
I simplified my problem description. Actually, the complex-alias needed
was:
<alias name="amountDue">
<complex-alias operator="-">
<complex-alias-field entity-alias="SalvageAmounts"
field="calculatedSalvageGross"/>
<complex-alias-field entity-alias="VehiclePaymentSummaryView"
field="vehiclePaymentTotalAmount"/>
</complex-alias>
</alias>
Where:
- SalvageAmounts is an entity that sits on top of a database view, not a
table.
- VehiclePaymentSummaryView is a view-entity with an aggregate function.
Running the report results in the error:
---- exception report
----------------------------------------------------------
Failure in by condition find operation, rolling back transaction
Exception: org.ofbiz.entity.GenericDataSourceException
Message: SQL Exception while executing the following:SELECT
Uplift.ingUplNumber,
Uplift.strUplInsurer, Uplift.dtmUplPickupDate,
VehiclePaymentSummaryView.SUM_Ve
hiclePayment_ingAmount_, UpliftFlags.strFlag, UpliftFlags.blnAgreed,
UpliftFlags
.blnReviewed, SalvageAmounts.upliftPAVBand, SalvageAmounts.ingPercentage,
Salvag
eAmounts.calculatedSalvageNet, SalvageAmounts.calculatedSalvageVat,
SalvageAmoun
ts.calculatedSalvageGross, (SalvageAmounts.calculatedSalvageGross -
VehiclePayme
ntSummaryView.SUM(VehiclePayment.ingAmount)) FROM ((dbo.tblUplift Uplift
LEFT OU
TER JOIN (SELECT Uplift.ingUplNumber AS Uplift_ingUplNumber,
SUM(VehiclePayment.
ingAmount) AS SUM_VehiclePayment_ingAmount_ FROM dbo.tblUplift Uplift LEFT
OUTER
JOIN dbo.tblVehiclePayment VehiclePayment ON Uplift.ingUplNumber =
VehiclePayme
nt.ingUpliftNumber GROUP BY Uplift.ingUplNumber) VehiclePaymentSummaryView
ON Up
lift.ingUplNumber = VehiclePaymentSummaryView.Uplift_ingUplNumber) INNER
JOIN db
o.tblUpliftFlags UpliftFlags ON Uplift.ingUplNumber = UpliftFlags.ingUplift)
INN
ER JOIN dbo.vSalvageAmounts SalvageAmounts ON Uplift.ingUplNumber =
SalvageAmoun
ts.ingUplNumber WHERE (Uplift.ingUplNumber > ? AND UpliftFlags.strFlag = ?
AND U
pliftFlags.blnAgreed = ? AND UpliftFlags.blnReviewed = ? AND
Uplift.dtmUplPickup
Date IS NOT NULL) (Cannot find either column "VehiclePaymentSummaryView" or
the
user-defined function or aggregate "VehiclePaymentSummaryView.SUM", or the
name
is ambiguous.)
--
I think I will push my logic back into a database view. I find that using
pure entity and view-entity does not have the power needed to work with some
legacy data designs. For example other posts have mentioned wanting to
specify join conditions in view-link's which would be very useful.
Many thanks, Chris
Bob Morley wrote:
>
> I think I would create a view-entity that aliased all of the fields you
> have here and added a complex-alias for the difference of the actual and
> estimated net amounts. Something like --
>
> <alias name="actualEstimatedDelta">
> <complex-alias operator="-">
> <complex-alias-field entity-alias="E1"
> field="actualNetAmount"/>
> <complex-alias-field entity-alias="E1"
> field="estimatedNetAmount"/>
> </complex-alias>
> </alias>
>
> Then do your entity condition on this field in your v-e.
>
> <condition-expr field="actualEstimatedDelta" operator="greater"
> value="0"/>
>
> Bob
>
>
> snowc wrote:
>>
>> I have some legacy data that I am trying to access in ofbiz.
>>
>> <entity name="E1">
>> <field name="e1ID"/>
>> <field name="actualNetAmount"/>
>> <field name="estimatedNetAmount"/>
>> </entity>
>>
>> Conceptually, I want to filter as shown here:
>>
>> SELECT e1Id, actualNetAmount, estimatedNetAmount
>> FROM E1 WHERE actualNetAmount > estimatedNetAmount
>>
>> <entity-condition entity="E1">
>> <condition-expr field="actualNetAmount" operator="greater"
>> value="${this.estimatedNetAmount}"/>
>> </entity-condition>
>>
>> How can I achieve the equivalent of "this" is a entity condition
>> expression?
>>
>> I do not want to filter by iterating the list as the data volumes are
>> huge.
>>
>> One option I am thinking of is to create a SQL view and use that to
>> filter the data:
>>
>> SELECT
>> e1Id, actualNetAmount, estimatedNetAmount,
>> CASE
>> WHEN actualNetAmount > estimatedNetAmount THEN 1
>> ELSE 0
>> END CASE AS actualGTEstimated
>> FROM E1
>>
>> Maybe I could use DynamicViewEntities, but I was trying to use less
>> beanshell/java and more minilang.
>>
>> Many thanks in advance, Chris
>>
>>
>
>
--
View this message in context: http://www.nabble.com/entity-condition-expression-problem-tp25010865p25020621.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: entity condition expression problem
Posted by Bob Morley <rm...@emforium.com>.
I think I would create a view-entity that aliased all of the fields you have
here and added a complex-alias for the difference of the actual and
estimated net amounts. Something like --
<alias name="actualEstimatedDelta">
<complex-alias operator="-">
<complex-alias-field entity-alias="E1"
field="actualNetAmount"/>
<complex-alias-field entity-alias="E1"
field="estimatedNetAmount"/>
</complex-alias>
</alias>
Then do your entity condition on this field in your v-e.
<condition-expr field="actualEstimatedDelta" operator="greater" value="0"/>
Bob
snowc wrote:
>
> I have some legacy data that I am trying to access in ofbiz.
>
> <entity name="E1">
> <field name="e1ID"/>
> <field name="actualNetAmount"/>
> <field name="estimatedNetAmount"/>
> </entity>
>
> Conceptually, I want to filter as shown here:
>
> SELECT e1Id, actualNetAmount, estimatedNetAmount
> FROM E1 WHERE actualNetAmount > estimatedNetAmount
>
> <entity-condition entity="E1">
> <condition-expr field="actualNetAmount" operator="greater"
> value="${this.estimatedNetAmount}"/>
> </entity-condition>
>
> How can I achieve the equivalent of "this" is a entity condition
> expression?
>
> I do not want to filter by iterating the list as the data volumes are
> huge.
>
> One option I am thinking of is to create a SQL view and use that to filter
> the data:
>
> SELECT
> e1Id, actualNetAmount, estimatedNetAmount,
> CASE
> WHEN actualNetAmount > estimatedNetAmount THEN 1
> ELSE 0
> END CASE AS actualGTEstimated
> FROM E1
>
> Maybe I could use DynamicViewEntities, but I was trying to use less
> beanshell/java and more minilang.
>
> Many thanks in advance, Chris
>
>
--
View this message in context: http://www.nabble.com/entity-condition-expression-problem-tp25010865p25018276.html
Sent from the OFBiz - User mailing list archive at Nabble.com.