| JOIN Syntax Worked in toplink essentials [message #976771] |
Thu, 08 November 2012 21:03  |
 Mike Phillips
Messages: 5
Registered: November 2012 |
Junior Member |
|
|
I am trying to switch to eclipselink but an existing named query fails that
I used to use in toplink essentials and I can't figure out how to construct
the JOIN.
eclipselink outputs "T5"."CLIENT_ID"."T5"."CLIENT_ID" and it should be something like
"T5"."CLIENT_ID"
Is this a bug, or should I just be using different syntax?
If I remove the "JOIN a.clientIdCollection cc" and the "AND ac.clientId=cc.clientId"
the query works but does not produce the results I want.
@NamedQuery(name = "DmeAccount.hasFilePathAccess", query = "SELECT COUNT(a) FROM AssetCore ac, DmeAccount a JOIN a.clientIdCollection cc" +
" WHERE ac.assetDigitalStorage.fileName=:fileName AND a.userId=:userId AND ac.securityLevelId.id<=a.securityLevelId.id AND a.accountStatus <> 'DELETED' AND a.accountLocked=0 AND a.accountDisabled=0 and a.expirePwdOn=0 AND ac.clientId=cc.clientId"),
# Genenerated SQL from stack trace
Error Code: 904
Call: SELECT COUNT(t0.USER_ID) FROM DME_DB_SCHEMA.DMEACCOUNT_DMECLIENT t6, DME_DB_SCHEMA.DMECLIENT t5, DME_DB_SCHEMA.LU_SECURITY_LEVEL t4, DME_DB_SCHEMA.LU_SECURITY_LEVEL t3, DME_DB_SCHEMA.ASSET_CORE t2, DME_DB_SCHEMA.ASSET_DIGITAL_STORAGE t1, DME_DB_SCHEMA.DMEACCOUNT t0 WHERE (((((((((t1.FILE_NAME = ?) AND (t0.USER_ID = ?)) AND (t3.ID <= t4.ID)) AND (t0.ACCOUNT_STATUS <> ?)) AND (t0.IS_LOCKED = ?)) AND (t0.IS_DISABLED = ?)) AND (t0.IS_EXPIRE_PWD_ON = ?)) AND (t2.CLIENT_ID = t5.CLIENT_ID.t5.CLIENT_ID)) AND ((((t1.ASSET_ID = t2.ASSET_ID) AND (t3.ID = t2.SECURITY_LEVEL_ID)) AND (t4.ID = t0.SECURITY_LEVEL_ID)) AND ((t6.USER_ID = t0.USER_ID) AND (t5.CLIENT_ID = t6.CLIENT_ID))))
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "T5"."CLIENT_ID"."T5"."CLIENT_ID": invalid identifier
[Updated on: Wed, 14 November 2012 00:53] Report message to a moderator |
|
|
|
|
| Re: JOIN Syntax Worked in toplink essentials [message #985980 is a reply to message #976771] |
Fri, 16 November 2012 22:06  |
Mike Phillips
Messages: 5
Registered: November 2012 |
Junior Member |
|
|
I was able to test with eclipselink 2.4. It is still broken.
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "T1"."CLIENT_ID"."T1"."CLIENT_ID": invalid identifier
Error Code: 904
Call: SELECT COUNT(t0.USER_ID) FROM DME_DB_SCHEMA.DMEACCOUNT_DMECLIENT t6, DME_DB_SCHEMA.LU_SECURITY_LEVEL t5, DME_DB_SCHEMA.LU_SECURITY_LEVEL t4, DME_DB_SCHEMA.ASSET_CORE t3, DME_DB_SCHEMA.ASSET_DIGITAL_STORAGE t2, DME_DB_SCHEMA.DMECLIENT t1, DME_DB_SCHEMA.DMEACCOUNT t0 WHERE (((((((((t2.FILE_NAME = ?) AND (t0.USER_ID = ?)) AND (t4.ID <= t5.ID)) AND (t0.ACCOUNT_STATUS <> ?)) AND (t0.IS_LOCKED = ?)) AND (t0.IS_DISABLED = ?)) AND (t0.IS_EXPIRE_PWD_ON = ?)) AND (t3.CLIENT_ID = t1.CLIENT_ID.t1.CLIENT_ID)) AND ((((t2.ASSET_ID = t3.ASSET_ID) AND (t4.ID = t3.SECURITY_LEVEL_ID)) AND (t5.ID = t0.SECURITY_LEVEL_ID)) AND ((t6.USER_ID = t0.USER_ID) AND (t1.CLIENT_ID = t6.CLIENT_ID))))
bind => [6 parameters bound]
Query: ReportQuery(name="DmeAccount.hasFilePathAccess" referenceClass=AssetCore sql="SELECT COUNT(t0.USER_ID) FROM DME_DB_SCHEMA.DMEACCOUNT_DMECLIENT t6, DME_DB_SCHEMA.LU_SECURITY_LEVEL t5, DME_DB_SCHEMA.LU_SECURITY_LEVEL t4, DME_DB_SCHEMA.ASSET_CORE t3, DME_DB_SCHEMA.ASSET_DIGITAL_STORAGE t2, DME_DB_SCHEMA.DMECLIENT t1, DME_DB_SCHEMA.DMEACCOUNT t0 WHERE (((((((((t2.FILE_NAME = ?) AND (t0.USER_ID = ?)) AND (t4.ID <= t5.ID)) AND (t0.ACCOUNT_STATUS <> ?)) AND (t0.IS_LOCKED = ?)) AND (t0.IS_DISABLED = ?)) AND (t0.IS_EXPIRE_PWD_ON = ?)) AND (t3.CLIENT_ID = t1.CLIENT_ID.t1.CLIENT_ID)) AND ((((t2.ASSET_ID = t3.ASSET_ID) AND (t4.ID = t3.SECURITY_LEVEL_ID)) AND (t5.ID = t0.SECURITY_LEVEL_ID)) AND ((t6.USER_ID = t0.USER_ID) AND (t1.CLIENT_ID = t6.CLIENT_ID))))")
|
|
|
|
| Re: JOIN Syntax Worked in toplink essentials [message #986520 is a reply to message #986484] |
Tue, 20 November 2012 17:49 |
Mike Phillips
Messages: 5
Registered: November 2012 |
Junior Member |
|
|
@Entity
@Table(name = "DMEACCOUNT",schema="DME_DB_SCHEMA")
@NamedQueries(
{
...
})
public class DmeAccount implements Serializable, Comparable, DmeEntityInterface<String>
...
@JoinTable(name = "DMEACCOUNT_DMECLIENT", schema="DME_DB_SCHEMA",joinColumns = {
@JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID")
}, inverseJoinColumns = {
@JoinColumn(name = "CLIENT_ID", referencedColumnName = "CLIENT_ID")
})
@ManyToMany
private List<DmeClient> clientIdCollection;
###------------------------------------###
@Entity
@Table(name = "DMECLIENT",schema="DME_DB_SCHEMA")
@SequenceGenerator(name="client_id_sequence",
sequenceName="DME_DB_SCHEMA.CLIENT_ID_SEQ",
allocationSize=1)
@NamedQueries(
{
...
})
public class DmeClient implements Serializable, Comparable, DmeEntityInterface<Long>
{
...
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="client_id_sequence")
@Column(name = "CLIENT_ID", nullable = false)
private Long clientId;
|
|
|
| Re: JOIN Syntax Worked in toplink essentials [message #987689 is a reply to message #986520] |
Tue, 27 November 2012 15:50 |
|
Sorry, I did not notice the SQL,
t1.CLIENT_ID.t1.CLIENT_ID
That is very odd, I have never seen that before. It seems like the column's table was somehow set to the full column name. The only thing I can think of is it might have something to do will the schema your setting. You could try removing this.
You did not include the mapping for the table, ASSET_CORE, which is causing the issue in its join. Please provide its table.
If you can create an isolated reproducible test case, please log a bug.
James : Wiki : Book : Blog : Twitter
|
|
|
|
Powered by
FUDForum. Page generated in 0.03612 seconds
] 
Search
Help
Register
Login
Home
