SQL ORA-00904: invalid identifier - join issue-CodePudding

have a problem with my query:

select Sources.dataset.name setName, x.element_name Commodity_Is, y.element_name Provider_Is
from meta.object_meta_v x, meta.object_meta_v y

join dw.load_set2_curve on x.object_id = dw.load_set2_curve.curve_id
join Sources.dataset on Sources.dataset.id =  dw.load_set2_curve.load_set_id
where dw.load_set2_curve.curve_id in (
    select max(curve_id) sample_curve_id from dw.load_Set2_curve
    group by load_set_id
)
and (meta.object_meta_v.attribute = 'Provider' or meta.object_meta_v.attribute = 'Commodity');

the error is on the line:

 join dw.load_set2_curve on x.object_id = dw.load_set2_curve.curve_id

I know why, because, according to this article 'https://stackoverflow.com/questions/10500048/invalid-identifier-on-oracle-inner-join' - "Looks like you cannot refer to an outer table alias in the join condition of the inner query." Unfortunately, I don't know how to find a workaround as I am looking for two different records (Commodity_is and Provider_is) from the same table in my query (with aliases 'x' and 'y'). Do you have any hints?

CodePudding user response：

Your problem is that you are not using the table aliases in the SELECT, ON and WHERE clauses and are trying to refer to identifiers as schema.table.column and, in some cases that is ambiguous and you need to use table_alias.column.

Additionally, you are trying to mix legacy comma joins with ANSI joins (which does work but the comma joins need to be last, not first, so its easier just to use ANSI joins all the way through):

select ds.name setName,
       x.element_name Commodity_Is,
       y.element_name Provider_Is
from   meta.object_meta_v x
       CROSS JOIN meta.object_meta_v y
       INNER JOIN dw.load_set2_curve lsc
       ON x.object_id = lsc.curve_id
       INNER JOIN Sources.dataset ds
       ON ds.id =  lsc.load_set_id
where  lsc.curve_id in (
         select max(curve_id) sample_curve_id
         from   dw.load_Set2_curve
         group by load_set_id
       )
and    (  x.attribute = 'Provider'
       or y.attribute = 'Commodity');

Which, for the sample data:

CREATE TABLE meta.object_meta_v (object_id, element_name, attribute) AS
SELECT 1, 'A', 'Provider'  FROM DUAL UNION ALL
SELECT 2, 'B', 'Commodity' FROM DUAL;

CREATE TABLE dw.load_set2_curve (curve_id, load_set_id) AS
SELECT 1, 100 FROM DUAL UNION ALL
SELECT 2, 200 FROM DUAL;

CREATE TABLE sources.dataset (id, name) AS
SELECT 100, 'DS1' FROM DUAL UNION ALL
SELECT 200, 'DS2' FROM DUAL;

Outputs:

SETNAME COMMODITY_IS PROVIDER_IS

DS1 A B

DS1 A A

DS2 B B

SETNAME	COMMODITY_IS	PROVIDER_IS
DS1	A	B
DS1	A	A
DS2	B	B

db<>fiddle here