HOWTO express an SQL UNION

[Valeriu Nedelcu <vn...@optaros.com>]

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

Re: HOWTO express an SQL UNION

[Chris Howe <cj...@yahoo.com>]

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 <vn...@optaros.com> 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
>