Home » Eclipse Projects » EclipseLink » left join implementation for informix problem
| left join implementation for informix problem [message #388105] |
Thu, 21 May 2009 15:38  |
 Janusz
Messages: 1
Registered: July 2009 |
Junior Member |
|
|
Hi,
I'm using Sun Java System Application Server 9.1_02, Java Persistence
implementation is toplink included with the server.
My Infomix version is IDS 11.50
The following Java Persistance query:
SELECT DISTINCT a.key1, a.key2, a.key3, a.key4, c.preference, ......
FROM Product a LEFT JOIN a.refSupHeader b LEFT JOIN b.refSup c
WHERE a.type = ?1
ORDER BY a.key1, a.key2, a.key3, a.key4, c.rowNb
for Informix is translated as:
SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE,
.... FROM p_product t0, OUTER p_ref_sup_header t2, p_ref_sup t1
WHERE ((t0.TYPE = ?) AND ((t2.ID = t0.p_ref_sup_header_id) AND
(t1.p_ref_sup_header_id = t2.ID)))
ORDER BY t0.KEY1 ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
bind => [MAT]
what is totaly wrong. Infomix supports LEFT JOIN, why Java Persistance is
using old OUTER syntax?
The proper query syntax, generated by JP for other database servers is:
SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE, ......
FROM p_product t0 LEFT OUTER JOIN p_ref_sup_header t2 ON (t2.ID =
t0.p_ref_sup_header_id)
LEFT OUTER JOIN p_ref_sup t1 ON (t1.p_ref_sup_header_id
= t2.ID)
WHERE (t0.TYPE = ?) ORDER BY t0.KEY1 ASC, t0.KEY2 ASC, t0.KEY3 ASC,
t0.KEY4 ASC, t1.row_nb ASC
bind => [MAT]
The above query works properly in infomix database when executed in
dbaccess tool.
Is there a way to force Java Persistence to generate right query syntax
like above?
Thank you very match for any help.
Janusz
|
|
|
| Re: left join implementation for informix problem [message #388109 is a reply to message #388105] |
Fri, 22 May 2009 13:30  |
Tom Eugelink
Messages: 817
Registered: July 2009 |
Senior Member |
|
|
I also use Eclipselink on top of Informix.
But even though the SQL syntax is old, as long as Informix supports it, it is not wrong. Are you actually receiving different results from the two queries?
Tom
Janusz wrote:
> Hi,
>
>
> I'm using Sun Java System Application Server 9.1_02, Java Persistence
> implementation is toplink included with the server.
> My Infomix version is IDS 11.50
>
> The following Java Persistance query:
>
> SELECT DISTINCT a.key1, a.key2, a.key3, a.key4, c.preference, ......
> FROM Product a LEFT JOIN a.refSupHeader b LEFT JOIN b.refSup c
> WHERE a.type = ?1 ORDER BY a.key1, a.key2, a.key3, a.key4, c.rowNb
>
> for Informix is translated as:
>
> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE,
> ... FROM p_product t0, OUTER p_ref_sup_header t2, p_ref_sup t1
> WHERE ((t0.TYPE = ?) AND ((t2.ID = t0.p_ref_sup_header_id) AND
> (t1.p_ref_sup_header_id = t2.ID))) ORDER BY t0.KEY1 ASC, t0.KEY2
> ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
> bind => [MAT]
>
> what is totaly wrong. Infomix supports LEFT JOIN, why Java Persistance
> is using old OUTER syntax?
>
> The proper query syntax, generated by JP for other database servers is:
>
> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE, ......
> FROM p_product t0 LEFT OUTER JOIN p_ref_sup_header t2 ON (t2.ID =
> t0.p_ref_sup_header_id) LEFT OUTER JOIN p_ref_sup t1
> ON (t1.p_ref_sup_header_id = t2.ID) WHERE (t0.TYPE = ?) ORDER BY t0.KEY1
> ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
> bind => [MAT]
>
> The above query works properly in infomix database when executed in
> dbaccess tool. Is there a way to force Java Persistence to generate
> right query syntax like above?
>
> Thank you very match for any help.
> Janusz
>
|
|
|
| Re: left join implementation for informix problem [message #388113 is a reply to message #388109] |
Mon, 25 May 2009 09:50 |
Janusz
Messages: 3
Registered: July 2009 |
Junior Member |
|
|
Yes, result of the query is wrong - it doesnt load p_product records where
no record in p_ref_sup exists. Probably (?) because OUTER is missing in
front of "p_ref_sup t1 " in FROM clause of the generated query.
Janusz
tbee wrote:
> I also use Eclipselink on top of Informix.
> But even though the SQL syntax is old, as long as Informix supports it, it
is not wrong. Are you actually receiving different results from the two
queries?
> Tom
> Janusz wrote:
>> Hi,
>>
>>
>> I'm using Sun Java System Application Server 9.1_02, Java Persistence
>> implementation is toplink included with the server.
>> My Infomix version is IDS 11.50
>>
>> The following Java Persistance query:
>>
>> SELECT DISTINCT a.key1, a.key2, a.key3, a.key4, c.preference, ......
>> FROM Product a LEFT JOIN a.refSupHeader b LEFT JOIN b.refSup c
>> WHERE a.type = ?1 ORDER BY a.key1, a.key2, a.key3, a.key4, c.rowNb
>>
>> for Informix is translated as:
>>
>> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE,
>> ... FROM p_product t0, OUTER p_ref_sup_header t2, p_ref_sup t1
>> WHERE ((t0.TYPE = ?) AND ((t2.ID = t0.p_ref_sup_header_id) AND
>> (t1.p_ref_sup_header_id = t2.ID))) ORDER BY t0.KEY1 ASC, t0.KEY2
>> ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
>> bind => [MAT]
>>
>> what is totaly wrong. Infomix supports LEFT JOIN, why Java Persistance
>> is using old OUTER syntax?
>>
>> The proper query syntax, generated by JP for other database servers is:
>>
>> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE, ......
>> FROM p_product t0 LEFT OUTER JOIN p_ref_sup_header t2 ON (t2.ID =
>> t0.p_ref_sup_header_id) LEFT OUTER JOIN p_ref_sup t1
>> ON (t1.p_ref_sup_header_id = t2.ID) WHERE (t0.TYPE = ?) ORDER BY t0.KEY1
>> ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
>> bind => [MAT]
>>
>> The above query works properly in infomix database when executed in
>> dbaccess tool. Is there a way to force Java Persistence to generate
>> right query syntax like above?
>>
>> Thank you very match for any help.
>> Janusz
>>
|
|
|
| Re: left join implementation for informix problem [message #388114 is a reply to message #388113] |
Mon, 25 May 2009 15:02 |
Tom Eugelink
Messages: 817
Registered: July 2009 |
Senior Member |
|
|
I have not run into problems so far. But I figure it should not be that difficult to copy-paste the generation code for, say, Oracle to the Informix class. Do you know since which version IFX supports the ANSI notations?
Tom
Janusz wrote:
> Yes, result of the query is wrong - it doesnt load p_product records
> where no record in p_ref_sup exists. Probably (?) because OUTER is
> missing in front of "p_ref_sup t1 " in FROM clause of the generated query.
>
> Janusz
>
>
>
> tbee wrote:
>
>> I also use Eclipselink on top of Informix.
>
>> But even though the SQL syntax is old, as long as Informix supports
>> it, it
> is not wrong. Are you actually receiving different results from the two
> queries?
>
>> Tom
>
>
>
>
>> Janusz wrote:
>>> Hi,
>>>
>>>
>>> I'm using Sun Java System Application Server 9.1_02, Java Persistence
>>> implementation is toplink included with the server.
>>> My Infomix version is IDS 11.50
>>>
>>> The following Java Persistance query:
>>>
>>> SELECT DISTINCT a.key1, a.key2, a.key3, a.key4, c.preference, ......
>>> FROM Product a LEFT JOIN a.refSupHeader b LEFT JOIN b.refSup c
>>> WHERE a.type = ?1 ORDER BY a.key1, a.key2, a.key3, a.key4,
>>> c.rowNb
>>>
>>> for Informix is translated as:
>>>
>>> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4,
>>> t1.PREFERENCE, ... FROM p_product t0, OUTER p_ref_sup_header t2,
>>> p_ref_sup t1 WHERE ((t0.TYPE = ?) AND ((t2.ID =
>>> t0.p_ref_sup_header_id) AND (t1.p_ref_sup_header_id = t2.ID)))
>>> ORDER BY t0.KEY1 ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC,
>>> t1.row_nb ASC
>>> bind => [MAT]
>>> what is totaly wrong. Infomix supports LEFT JOIN, why Java
>>> Persistance is using old OUTER syntax?
>>>
>>> The proper query syntax, generated by JP for other database servers is:
>>>
>>> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE,
>>> ......
>>> FROM p_product t0 LEFT OUTER JOIN p_ref_sup_header t2 ON (t2.ID =
>>> t0.p_ref_sup_header_id) LEFT OUTER JOIN p_ref_sup
>>> t1 ON (t1.p_ref_sup_header_id = t2.ID) WHERE (t0.TYPE = ?) ORDER BY
>>> t0.KEY1 ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
>>> bind => [MAT]
>>> The above query works properly in infomix database when executed
>>> in dbaccess tool. Is there a way to force Java Persistence to
>>> generate right query syntax like above?
>>>
>>> Thank you very match for any help.
>>> Janusz
>>>
>
>
|
|
| | | | |
Goto Forum:
Current Time: Tue Apr 23 14:57:37 GMT 2024
Powered by FUDForum. Page generated in 0.04798 seconds |
] 
Search
Help
Register
Login
Home
