| JOIN Syntax Worked in toplink essentials [message #976771] |
Thu, 08 November 2012 16:03  |
Eclipse User |
|
|
|
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: Tue, 13 November 2012 19:53] by Moderator |
|
|
|
|
| Re: JOIN Syntax Worked in toplink essentials [message #985980 is a reply to message #976771] |
Fri, 16 November 2012 17:06  |
| Eclipse User |
|
|
|
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 12:49 |
| Eclipse User |
|
|
|
@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 #1020797 is a reply to message #987689] |
Mon, 18 March 2013 19:20 |
| Eclipse User |
|
|
|
Just for closure, I finally found the syntax that I could change to correct the problem
I was using the join alias cc and adding .clientId when I did not have to.
join alias "JOIN a.clientIdCollection cc" offending portion of query "AND ac.clientId=cc.clientId". all I had to do is change to "AND ac.clientId=cc".
So the final fixed query again:
@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")
Thanks for taking a look.
|
|
|
Powered by
FUDForum. Page generated in 0.04171 seconds
Home