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.
