Hi all,
I'm trying to use the following SQL Query:

@NamedNativeQuery(name="archive&file", query=" SELECT id, "+
" sender_id, "+
" date_created "+
" FROM DOCUMENT_ARCHIVE da "+
" where da.SOURCE_FILE_NAME = 'shpaig0140_shpsts_shpaig_C2KRMP1.txt' "+
" union all "+
" select file_id,"+
" sender_id,"+
" date_created "+
" from DOCUMENT_FILE df "+
" WHERE df.SOURCE_FILE_NAME = 'shpaig0140_shpsts_shpaig_C2KRMP1.txt' ",
resultSetMapping="joinMapping",resultClass=DocumentArchive.class)
@SqlResultSetMapping(name="joinMapping", entities={
@EntityResult(entityClass=com.kn.ms.tracking.client.DocumentArchive.class, fields= {
@FieldResult(name="id", column="id"),
@FieldResult(name="sender_id", column="sender_id"),
@FieldResult(name="date_created", column="date_created"),
}),
@EntityResult(entityClass=com.kn.ms.tracking.client.DocumentFile.class, fields = {
@FieldResult(name="file_id", column="id"),
@FieldResult(name="sender_id", column="sender_id"),
@FieldResult(name="date_created", column="date_created"),
})
}
)

Both tables does not have any relationship each other. I just want to look in one query for a SOURCE_FILE_NAME. This SQL works directly from Oracle Developer but whenever I'm trying to run it with JPA/Hibernate Impl. I'm getting the error:

org.hibernate.util.JDBCExceptionReporter - SQL Error: 17006, SQLState: 99999
Undefined Column.

The code used: Query queryR = getEntityManager().createNamedQuery("archive&file");

The Objects:
/**
*
*/
package com.kn.ms.tracking.client;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;



@NamedQueries( {
@NamedQuery(name = "getAllDocumentFiles",query = "SELECT c FROM DocumentFile c"),
@NamedQuery(name = "rowCountQueryDocumentFiles",query = "SELECT count(c) FROM DocumentFile c")
})

@Entity
@Table(name="DOCUMENT_FILE")
public class DocumentFile extends AbstractDTO implements Serializable {
/**
*
*/
private static final long serialVersionUID = 2374831861337326844L;
/**
*
*/

@Id
private String file_id;
private Date date_received;
private String source_file_name;
private String sender_id;
private Integer status;
private Date date_created;
private String source_hub;
private String source_encoding;
private String internal_directory;
private String transport_type;
private Date retention_period;
private Integer content_length;
/**
* @return the file_id
*/
public String getFile_id() {
return file_id;
}
/**
* @param fileId the file_id to set
*/
public void setFile_id(String fileId) {
file_id = fileId;
}
/**
* @return the date_received
*/
public Date getDate_received() {
return date_received;
}
/**
* @param dateReceived the date_received to set
*/
public void setDate_received(Date dateReceived) {
date_received = dateReceived;
}
/**
* @return the source_file_name
*/
public String getSource_file_name() {
return source_file_name;
}
/**
* @param sourceFileName the source_file_name to set
*/
public void setSource_file_name(String sourceFileName) {
source_file_name = sourceFileName;
}
/**
* @return the sender_id
*/
public String getSender_id() {
return sender_id;
}
/**
* @param senderId the sender_id to set
*/
public void setSender_id(String senderId) {
sender_id = senderId;
}
/**
* @return the status
*/
public Integer getStatus() {
return status;
}
/**
* @param status the status to set
*/
public void setStatus(Integer status) {
this.status = status;
}
/**
* @return the date_created
*/
public Date getDate_created() {
return date_created;
}
/**
* @param dateCreated the date_created to set
*/
public void setDate_created(Date dateCreated) {
date_created = dateCreated;
}
/**
* @return the source_hub
*/
public String getSource_hub() {
return source_hub;
}
/**
* @param sourceHub the source_hub to set
*/
public void setSource_hub(String sourceHub) {
source_hub = sourceHub;
}
/**
* @return the source_encoding
*/
public String getSource_encoding() {
return source_encoding;
}
/**
* @param sourceEncoding the source_encoding to set
*/
public void setSource_encoding(String sourceEncoding) {
source_encoding = sourceEncoding;
}
/**
* @return the internal_directory
*/
public String getInternal_directory() {
return internal_directory;
}
/**
* @param internalDirectory the internal_directory to set
*/
public void setInternal_directory(String internalDirectory) {
internal_directory = internalDirectory;
}
/**
* @return the transport_type
*/
public String getTransport_type() {
return transport_type;
}
/**
* @param transportType the transport_type to set
*/
public void setTransport_type(String transportType) {
transport_type = transportType;
}
/**
* @return the retention_period
*/
public Date getRetention_period() {
return retention_period;
}
/**
* @param retentionPeriod the retention_period to set
*/
public void setRetention_period(Date retentionPeriod) {
retention_period = retentionPeriod;
}
/**
* @return the content_length
*/
public Integer getContent_length() {
return content_length;
}
/**
* @param contentLength the content_length to set
*/
public void setContent_length(Integer contentLength) {
content_length = contentLength;
}
/**
* @return the serialversionuid
*/
public static long getSerialversionuid() {
return serialVersionUID;
}



}

==================================================
/**
*
*/
package com.kn.ms.tracking.client;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FetchType;
import javax.persistence.FieldResult;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.Table;



@NamedQueries( {
@NamedQuery(name = "getAll",query = "SELECT c FROM DocumentArchive c"),
@NamedQuery(name = "rowCountQuery",query = "SELECT count(c) FROM DocumentArchive c")
})

@Entity
@Table(name="DOCUMENT_ARCHIVE")

@NamedNativeQuery(name="archive&file", query=" SELECT id, "+
" sender_id, "+
" date_created "+
" FROM DOCUMENT_ARCHIVE da "+
" where da.SOURCE_FILE_NAME = 'shpaig0140_shpsts_shpaig_C2KRMP1.txt' "+
" union all "+
" select file_id,"+
" sender_id,"+
" date_created "+
" from DOCUMENT_FILE df "+
" WHERE df.SOURCE_FILE_NAME = 'shpaig0140_shpsts_shpaig_C2KRMP1.txt' ",
resultSetMapping="joinMapping",resultClass=DocumentArchive.class)
@SqlResultSetMapping(name="joinMapping", entities={
@EntityResult(entityClass=com.kn.ms.tracking.client.DocumentArchive.class, fields= {
@FieldResult(name="id", column="id"),
@FieldResult(name="sender_id", column="sender_id"),
@FieldResult(name="date_created", column="date_created"),
}),
@EntityResult(entityClass=com.kn.ms.tracking.client.DocumentFile.class, fields = {
@FieldResult(name="file_id", column="id"),
@FieldResult(name="sender_id", column="sender_id"),
@FieldResult(name="date_created", column="date_created"),
})
}
)

public class DocumentArchive extends AbstractDTO implements Serializable {

@Id
private String id;

private Integer document_type;
private Date date_received;
private String source_file_name;
private String file_id;
private String sender_id;
private String receiver_id;
private Integer status;
private String message_type;
private String target_hub;
private Integer delivery_mode;

@Basic(fetch = FetchType.LAZY)
@Column(name = "PHYSICAL_SENDER", columnDefinition = "char")
private String physical_sender;

@Basic(fetch = FetchType.LAZY)
@Column(name = "PHYSICAL_RECEIVER", columnDefinition = "char")
private String physical_receiver;

private String logical_receiver;
private Integer priority;
private String target_file_name;

@Column(name = "SOURCE_ENCODING", columnDefinition = "char")
private String source_encoding;
@Column(name = "TARGET_ENCODING", columnDefinition = "char")
private String target_encoding;

@Column(name = "RECEIVER_UPDATE", columnDefinition = "char")
private String receiver_update;

@Column(name = "SOURCE_HEADER_TYPE", columnDefinition = "char")
private String source_header_type;
private Integer content_length;
private Date date_delivered;
private String routing_entry;
private String outbound_file_id;
private String internal_directory;
private Date retention_period;
private Date date_created;
private String logical_sender;
private String transform_xml;
private String transform_xslt;
/**
* @return the id
*/
public String getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(String id) {
this.id = id;
}
/**
* @return the document_type
*/
public Integer getDocument_type() {
return document_type;
}
/**
* @param documentType the document_type to set
*/
public void setDocument_type(Integer documentType) {
document_type = documentType;
}
/**
* @return the date_received
*/
public Date getDate_received() {
return date_received;
}
/**
* @param dateReceived the date_received to set
*/
public void setDate_received(Date dateReceived) {
date_received = dateReceived;
}
/**
* @return the source_file_name
*/
public String getSource_file_name() {
return source_file_name;
}
/**
* @param sourceFileName the source_file_name to set
*/
public void setSource_file_name(String sourceFileName) {
source_file_name = sourceFileName;
}
/**
* @return the file_id
*/
public String getFile_id() {
return file_id;
}
/**
* @param fileId the file_id to set
*/
public void setFile_id(String fileId) {
file_id = fileId;
}
/**
* @return the sender_id
*/
public String getSender_id() {
return sender_id;
}
/**
* @param senderId the sender_id to set
*/
public void setSender_id(String senderId) {
sender_id = senderId;
}
/**
* @return the receiver_id
*/
public String getReceiver_id() {
return receiver_id;
}
/**
* @param receiverId the receiver_id to set
*/
public void setReceiver_id(String receiverId) {
receiver_id = receiverId;
}
/**
* @return the status
*/
public Integer getStatus() {
return status;
}
/**
* @param status the status to set
*/
public void setStatus(Integer status) {
this.status = status;
}
/**
* @return the message_type
*/
public String getMessage_type() {
return message_type;
}
/**
* @param messageType the message_type to set
*/
public void setMessage_type(String messageType) {
message_type = messageType;
}
/**
* @return the target_hub
*/
public String getTarget_hub() {
return target_hub;
}
/**
* @param targetHub the target_hub to set
*/
public void setTarget_hub(String targetHub) {
target_hub = targetHub;
}
/**
* @return the delivery_mode
*/
public Integer getDelivery_mode() {
return delivery_mode;
}
/**
* @param deliveryMode the delivery_mode to set
*/
public void setDelivery_mode(Integer deliveryMode) {
delivery_mode = deliveryMode;
}
/**
* @return the physical_sender
*/
public String getPhysical_sender() {
return physical_sender;
}
/**
* @param physicalSender the physical_sender to set
*/
public void setPhysical_sender(String physicalSender) {
physical_sender = physicalSender;
}

/**
* @return the logical_receiver
*/
public String getLogical_receiver() {
return logical_receiver;
}
/**
* @param logicalReceiver the logical_receiver to set
*/
public void setLogical_receiver(String logicalReceiver) {
logical_receiver = logicalReceiver;
}
/**
* @return the priority
*/
public Integer getPriority() {
return priority;
}
/**
* @param priority the priority to set
*/
public void setPriority(Integer priority) {
this.priority = priority;
}
/**
* @return the target_file_name
*/
public String getTarget_file_name() {
return target_file_name;
}
/**
* @param targetFileName the target_file_name to set
*/
public void setTarget_file_name(String targetFileName) {
target_file_name = targetFileName;
}
/**
* @return the source_encoding
*/
public String getSource_encoding() {
return source_encoding;
}
/**
* @param sourceEncoding the source_encoding to set
*/
public void setSource_encoding(String sourceEncoding) {
source_encoding = sourceEncoding;
}
/**
* @return the target_encoding
*/
public String getTarget_encoding() {
return target_encoding;
}
/**
* @param targetEncoding the target_encoding to set
*/
public void setTarget_encoding(String targetEncoding) {
target_encoding = targetEncoding;
}
/**
* @return the receiver_update
*/
public String getReceiver_update() {
return receiver_update;
}
/**
* @param receiverUpdate the receiver_update to set
*/
public void setReceiver_update(String receiverUpdate) {
receiver_update = receiverUpdate;
}
/**
* @return the source_header_type
*/
public String getSource_header_type() {
return source_header_type;
}
/**
* @param sourceHeaderType the source_header_type to set
*/
public void setSource_header_type(String sourceHeaderType) {
source_header_type = sourceHeaderType;
}
/**
* @return the content_length
*/
public Integer getContent_length() {
return content_length;
}
/**
* @param contentLength the content_length to set
*/
public void setContent_length(Integer contentLength) {
content_length = contentLength;
}
/**
* @return the date_delivered
*/
public Date getDate_delivered() {
return date_delivered;
}
/**
* @param dateDelivered the date_delivered to set
*/
public void setDate_delivered(Date dateDelivered) {
date_delivered = dateDelivered;
}
/**
* @return the routing_entry
*/
public String getRouting_entry() {
return routing_entry;
}
/**
* @param routingEntry the routing_entry to set
*/
public void setRouting_entry(String routingEntry) {
routing_entry = routingEntry;
}
/**
* @return the outbound_file_id
*/
public String getOutbound_file_id() {
return outbound_file_id;
}
/**
* @param outboundFileId the outbound_file_id to set
*/
public void setOutbound_file_id(String outboundFileId) {
outbound_file_id = outboundFileId;
}
/**
* @return the internal_directory
*/
public String getInternal_directory() {
return internal_directory;
}
/**
* @param internalDirectory the internal_directory to set
*/
public void setInternal_directory(String internalDirectory) {
internal_directory = internalDirectory;
}
/**
* @return the retention_period
*/
public Date getRetention_period() {
return retention_period;
}
/**
* @param retentionPeriod the retention_period to set
*/
public void setRetention_period(Date retentionPeriod) {
retention_period = retentionPeriod;
}
/**
* @return the date_created
*/
public Date getDate_created() {
return date_created;
}
/**
* @param dateCreated the date_created to set
*/
public void setDate_created(Date dateCreated) {
date_created = dateCreated;
}
/**
* @return the logical_sender
*/
public String getLogical_sender() {
return logical_sender;
}
/**
* @param logicalSender the logical_sender to set
*/
public void setLogical_sender(String logicalSender) {
logical_sender = logicalSender;
}
/**
* @return the transform_xml
*/
public String getTransform_xml() {
return transform_xml;
}
/**
* @param transformXml the transform_xml to set
*/
public void setTransform_xml(String transformXml) {
transform_xml = transformXml;
}
/**
* @return the transform_xslt
*/
public String getTransform_xslt() {
return transform_xslt;
}
/**
* @param transformXslt the transform_xslt to set
*/
public void setTransform_xslt(String transformXslt) {
transform_xslt = transformXslt;
}
}

The NativeQuery has just to return a dummy resultSet.
Knows anybody about this? Is it possible to get result from 2 different tables using JPA/Hib?
Any concrete example will be helpful.

Best Regards,