entity condition expression problem

[chris snow]

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

[Bob Morley]

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

[chris snow]

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

[Bob Morley]

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