HOWTO express an SQL UNION

[Valeriu Nedelcu]

Hi all,

I'm confronted with the following problem:
Find all INVOICEs that were issued by OR from a PARTY and then display
some info about them.
In other words, I want to obtain a SQL similar with the following UNION:

SELECT sender_id, receiver_id, COUNT(invoice_id) FROM
(
    (SELECT INVOICE.party_id as receiver_id, INVOICE.party_id_from as
sender_id, INVOICE.invoice_id
        FROM PARTY P1 INNER JOIN INVOICE ON
P1.*party_id*=INVOICE.*party_id* WHERE P1.party_id=? AND
INVOICE.invoice_type_id='PURCHASE_INVOICE')
    UNION
    (SELECT INVOICE.party_id as receiver_id, INVOICE.party_id_from as
sender_id, INVOICE.invoice_id
        FROM PARTY P2 INNER JOIN INVOICE ON
P2.*party_id*=INVOICE.*party_id_from* WHERE P2.party_id=? AND
INVOICE.invoice_type_id='SALES_INVOICE')
) as PINV_UNION
GROUP BY sender_id, receiver_id
ORDER BY sender_id, receiver_id

I have looked into the available API for the DynamicViewEntity but could
not find anything on how to generate an UNION.

Do Entity Model/Engine support this kind of SQL generation?
If not, please suggest alternatives.


Regards,
Val

[cjhowe]

Hi Val,

The Screen Widget, Form Widget and Simple methods support
<entity-condition>

For your particular scenario you would want to do something like the
following

<entity-condition entity="Invoice" list="listIt">
 <condition-list combine="or">
  <condition-expr field-name="partyId" env-name="parameters.partyId"/>
  <condition-expr field-name="partyIdFrom"
env-name="parameters.partyId"/>
  </condition-list>
</entity-condition>
--- Valeriu Nedelcu <[hidden email]> wrote:

> Hi all,
>
> I'm confronted with the following problem:
> Find all INVOICEs that were issued by OR from a PARTY and then
> display
> some info about them.
> In other words, I want to obtain a SQL similar with the following
> UNION:
>
> SELECT sender_id, receiver_id, COUNT(invoice_id) FROM
> (
>     (SELECT INVOICE.party_id as receiver_id, INVOICE.party_id_from as
> sender_id, INVOICE.invoice_id
>         FROM PARTY P1 INNER JOIN INVOICE ON
> P1.*party_id*=INVOICE.*party_id* WHERE P1.party_id=? AND
> INVOICE.invoice_type_id='PURCHASE_INVOICE')
>     UNION
>     (SELECT INVOICE.party_id as receiver_id, INVOICE.party_id_from as
> sender_id, INVOICE.invoice_id
>         FROM PARTY P2 INNER JOIN INVOICE ON
> P2.*party_id*=INVOICE.*party_id_from* WHERE P2.party_id=? AND
> INVOICE.invoice_type_id='SALES_INVOICE')
> ) as PINV_UNION
> GROUP BY sender_id, receiver_id
> ORDER BY sender_id, receiver_id
>
> I have looked into the available API for the DynamicViewEntity but
> could
> not find anything on how to generate an UNION.
>
> Do Entity Model/Engine support this kind of SQL generation?
> If not, please suggest alternatives.
>
>
> Regards,
> Val
>

[cris2014]

Hi,
I would like to know how to perform an SQL UNION in ofbiz using DynamicViewEntity.
Is there any way?

In my union, one query is over ofbiz orders tables, and an other query is performed over a view-entity of an external table to get other orders.

Basically I have to merge all the orders in a common structure. Since I want perform a service which is able to handle paginated results with all orders sorted by date, I need to do a single query to sort all orders and then return the numbers of orders requested by the caller.

I would avoid a jdbc prepared statement.

Please give me any advice.