Hi,
I use Hibernate in my application as well as JPQL NamedQuery request. According to JPQL documentation, it is possible to use LEFT OUTER JOIN (in fact LEFT JOIN) between Tables that have a link in the hibernate mapping, but infortunately it seems that it is not possible to use LEFT OUTER JOIN by specifying a "ON" clause while it is possible in pure SQL
What I want to do and that doesn't work :
Code:
@NamedQuery(query = "SELECT " +
"m " +
"FROM RMessage m " +
"LEFT JOIN m.screen s " +
"LEFT JOIN s.RActivity a " +
"LEFT JOIN m.subject sub " +
"LEFT JOIN RActivityStatus actStat on (actStat.id.actId = a.actId and actStat.id.subId = sub.subId) " +
"LEFT JOIN RScreenstatus stat on (stat.id.scrId = s.scrId AND stat.id.subId = sub.subId AND stat.id.sstScrseq = m.sstScrseq), " +
"RMsgline ml, " +
"RMsgline ml1, " +
"WHERE " +
"ml1.message.msgId = m.msgId and ml1.id.mslId = (select min(ml2.id.mslId) from RMsgline ml2 where ml2.message.msgId = m.msgId) AND " +
"ml.message.msgId = m.msgId AND " +
"m.subject.subId = :subId AND " +
"a.actId = :actId AND " +
"s.scrId = :scrId AND " +
"m.sstScrseq = :scrSeq AND " +
"((m.fieldId is not null and actStat.status <> '777' and stat.sstStatus <> '777') OR (m.fieldId is null and m.msgCenter = 0 and actStat.status <> '777' and stat.sstStatus <> '777') OR m.msgCenter <> 0) AND " +
"m.msgType = 'Q' " +
"ORDER BY m.msgDuedt, ml1.id.mslId, ml.id.mslId",
name = "RMessage.findByStillOpenSubjectActivityAndScreen"),
The base Object is "RMessage". In fact the "JOIN"s will take effect only if the "msgCenter" of "RMessage" is equal to 0. That's why (it is an idea) I tried something as the following, but without success
Code:
@NamedQuery(query = "SELECT " +
"m " +
"FROM RMessage m " +
"LEFT JOIN m.screen s " +
"LEFT JOIN s.RActivity a " +
"LEFT JOIN m.subject sub, " +
"RMsgline ml, " +
"RMsgline ml1, " +
"RActivityStatus actStat, " +
"RScreenstatus stat " +
"WHERE " +
"(m.msgCenter <> 0 OR (m.msgCenter = 0 and stat.id.scrId = s.scrId AND stat.id.subId = sub.subId AND stat.id.sstScrseq = m.sstScrseq)) AND " +
"(m.msgCenter <> 0 OR (m.msgCenter = 0 and actStat.id.actId = a.actId and actStat.id.subId = sub.subId)) AND " +
"ml1.message.msgId = m.msgId and ml1.id.mslId = (select min(ml2.id.mslId) from RMsgline ml2 where ml2.message.msgId = m.msgId) AND " +
"ml.message.msgId = m.msgId AND " +
"m.subject.subId = :subId AND " +
"a.actId = :actId AND " +
"s.scrId = :scrId AND " +
"m.sstScrseq = :scrSeq AND " +
"((m.fieldId is not null and actStat.status <> '777' and stat.sstStatus <> '777') OR (m.fieldId is null and m.msgCenter = 0 and actStat.status <> '777' and stat.sstStatus <> '777') OR m.msgCenter <> 0) AND " +
"m.msgType = 'Q' " +
"ORDER BY m.msgDuedt, ml1.id.mslId, ml.id.mslId",
name = "RMessage.findByStillOpenSubjectActivityAndScreen"),
If you can help me, thanks in advance
Login
FAQ
Search
