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