Home » Eclipse Projects » EclipseLink » Incorrect SQL syntax for a "JOIN FETCH" JPQL(Incorrect SQL syntax for a "JOIN FETCH" JPQL)
|
Re: Incorrect SQL syntax for a "JOIN FETCH" JPQL [message #506978 is a reply to message #506963] |
Mon, 11 January 2010 15:34 |
|
You should get the outer join generated in the SQL.
Perhaps try, "LEFT OUTER JOIN FETCH j.broker"
Also what is the code for the real query, from the SQL it is more complex than the JPQL you gave. If you try the same JPQL as you gave, do it do the outer join?
You could also remove the JOIN FETCH from the JPQL, and instead use the EclipseLink query hint,
"eclipselink.left-join-fetch"="j.broker"
James : Wiki : Book : Blog : Twitter
|
|
|
Re: Incorrect SQL syntax for a "JOIN FETCH" JPQL [message #507001 is a reply to message #506978] |
Mon, 11 January 2010 16:33 |
Dan Siviter Messages: 4 Registered: January 2010 |
Junior Member |
|
|
Thanks for the prompt reply! You're correct the query is more complex, but I removed a load of code for clarity. But this time I performed the queries exactly how I've written them below.
James wrote on Mon, 11 January 2010 10:34 | You should get the outer join generated in the SQL.
Perhaps try, "LEFT OUTER JOIN FETCH j.broker"
|
Ok, so using:
SELECT j
FROM Job j
LEFT JOIN FETCH j.broker
I get:
SELECT t1.job_id, t1.start_time, t1.job_name, t1.broker_id, t0.broker_id, t0.broker_name
FROM dbo.jobs t1
LEFT OUTER JOIN dbo.brokers t0 ON (t0.broker_id = t1.broker_id)
So I thought I'd reintroduce the filter parameter:
SELECT j
FROM Job j
LEFT JOIN FETCH j.broker
WHERE j.broker.name = :brokerName
Gives:
SELECT t1.job_id, t1.job_name, t1.broker_id, t0.broker_id, t0.broker_name
FROM dbo.brokers t0, dbo.jobs t1
WHERE ((t0.broker_name = ?)
AND (t0.broker_id = t1.broker_id))
I thought I'd alias the broker to see if that would work:
SELECT j
FROM Job j
LEFT JOIN FETCH j.broker
JOIN j.broker AS b
WHERE b.name = :brokerName
Gives:
SELECT t1.job_id, t1.job_name, t1.broker_id, t0.broker_id, t0.broker_name
FROM dbo.brokers t0, dbo.jobs t1
WHERE ((t0.broker_name = ?)
AND (t0.broker_id = t1.broker_id))
Finally, I thought I'd try the WHERE clause on something on Job:
SELECT j
FROM Job j
LEFT JOIN FETCH j.broker
WHERE j.name = :brokerName
Gave:
SELECT t1.job_id, t1.job_name, t1.broker_id, t0.broker_id, t0.broker_name
FROM dbo.jobs t1
LEFT OUTER JOIN dbo.brokers t0 ON (t0.broker_id = t1.broker_id)
WHERE (t1.job_name = ?)
It seems when I attempt to do any WHERE clause on broker it prevents it from using 'LEFT OUTER JOIN'.
James wrote on Mon, 11 January 2010 10:34 | You could also remove the JOIN FETCH from the JPQL, and instead use the EclipseLink query hint,
"eclipselink.left-join-fetch"="j.broker"
|
I tried that also to no avail.
Any other ideas?
|
|
| |
Goto Forum:
Current Time: Fri Apr 26 19:35:06 GMT 2024
Powered by FUDForum. Page generated in 0.03262 seconds
|