JPQL union

[samir ware]

Hello all
I am trying to map below mention query in JAP.

select ID, DOC_REQ_DESC, DOC_REQ_REF, VERSION, DOC_TYPE, DOC_CATEGORY
 from COIM_DOC_REQ_TYPE master
 union select  ID, DOC_REQ_DESC, DOC_REQ_REF, VERSION, DOC_TYPE, DOC_CATEGORY  from COIMDOCREQTYPE_HIST history where history.action = 'Delete'
 

I have two seperate java classes viz one for master and other for history table. Though I am not getting how exactly should I write the JPQL query or rather which API I am suppose to use to grab the data from the query mentioned above.
Any link, white paper will greatly be appricaited.
Thanks
Samir

[James Sutherland]

This depend on how you have mapped the tables?

In general in JPA you can use SQL in native queries, so you can use the SQL if you wish.
You could also execute two queries.

JPQL does not support unions, but some JPA providers do,

EclipseLink supports unions in JPQL see,

http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#UNION

EclipseLink also has history support which may interest you,

http://wiki.eclipse.org/EclipseLink/Examples/JPA/History

[samir ware]

Hello James
Thank you so much for the reply.
I have two tables , one is for maintaining document information and other one is for maintaining operations on the documents. (e.g. update,delete) . Both are jsut replica of each other where one is maintaining others audit information.

Both the tables are mapped to two different objects.
I was trying to execute below mention code though I am getting "column not found" exception.
Can you please tell me where exactly I am missing ??

Query q = entityManager().createNativeQuery(
                "(select master.DOC_CATEGORY from COIM_DOC_REQ_TYPE master) union (select  history.DOC_CATEGORY from COIMDOCREQTYPE_HIST history) ",DocumentReqType.class);
        List result = q.getResultList();

[samir ware]

I am not sure what exctly is EclipseLink .. Though I am not using it . I am with bare JPA's API. . So eclipseLink is something on which I cant relay

[samir ware]

I have not done any explicit mapping except mapping columns to the specific fields as below.

 @Column(name="DOC_TYPE")
    private String documentType;

Do I need to do any explicit mapping if I am triggering union query.
I can execute other simple queries but not the one which has union in it
Thanks
Samir