Strange behaviour when executing query on Oracle with EclipseLink

Question

I created a namedquery as follows:

SELECT o
FROM TableName1Entity o , TableName2Entity a
WHERE o.field1 = a.field4
AND a.field5 = :param1

That is converted to the following sql:

SELECT TABLE_NAME1.FIELD1, TABLE_NAME1.FIELD2, TABLE_NAME1.FIELD3
FROM TABLE_NAME2 t0, TABLE_NAME1 t1
WHERE ((t0.FIELD4 = t1.FIELD1)
AND (t0.FIELD5 = ?));

And it returns ORA-00904: "TABLE_NAME1"."FIELD3": invalid identifier

But it works when I manually modify as follows: (they all work as expected)

SELECT t1.FIELD1, t1.FIELD2, t1.FIELD3
FROM TABLE_NAME2 t0, TABLE_NAME1 t1
WHERE ((t0.FIELD4 = t1.FIELD1)
AND (t0.FIELD5 = ?));

SELECT TABLE_NAME1.FIELD1, TABLE_NAME1.FIELD2, TABLE_NAME1.FIELD3
FROM TABLE_NAME2 t0, TABLE_NAME1
WHERE ((t0.FIELD4 = TABLE_NAME1.FIELD1)
AND (t0.FIELD5 = ?));

SELECT FIELD1, FIELD2, FIELD3
FROM TABLE_NAME2 t0, TABLE_NAME1 t1
WHERE ((t0.FIELD4 = t1.FIELD1) AND (t0.FIELD5 = ?));

SELECT FIELD1, FIELD2, FIELD3
FROM TABLE_NAME2 t0, TABLE_NAME1
WHERE ((t0.FIELD4 = TABLE_NAME1.FIELD1)
AND (t0.FIELD5 = ?));

PS.: for security reasons, I cant give table or field names.

The thing is: when the table name is explicitly set along with a label to it, the query doesn't work.

Please, don't give alternative solutions by changing the named query because it should be fine as is and changing the named query is not an option. unless it really has an error.

You did not show the named query; a named query belongs to the world of ORM and is written not in SQL (SQL having such an alias `label` for the table). Look at [what is a named query](http://stackoverflow.com/questions/4517069/what-is-a-named-query) — Joop Eggen, Jul 10 '12 at 13:43
and the JPA implementation is ? because conversion of a JPQL query into SQL is totally related to the implementation — DataNucleus, Jul 10 '12 at 15:07

score 1 · Answer 1 · answered Jul 10 '12 at 13:35

1

What you've done it's called aliasing: the table TABLE_NAME must be referred as label in the select part of the query because it's how the table is named (and seen) by the rest of the query.

So you have to write something like:

SELECT label.FIELD_NAME FROM TABLE_NAME label;

answered Jul 10 '12 at 13:35

Vincenzo Maggio

3,787
26
42

ok, but i don't have any control how the named query is converted. :/ the query is **automatically** converted just like the first example. – w35l3y Jul 10 '12 at 13:40

score 0 · Accepted Answer · answered Sep 12 '13 at 14:41

0

Change the version of EclipseLink from 1.1.1 to 2.0.0

answered Sep 12 '13 at 14:41

w35l3y

8,613
3
39
51

Strange behaviour when executing query on Oracle with EclipseLink

2 Answers2