I am trying fo factor a repetitive subquery with with
but getting ORA-00904
over and over again.
This is my base query which works quite well:
select tka.id, tka.name, txt.text as langname, einh.text as einheit, tka.typ,
vgd.a_wert, vgd.i_wert, vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
inner join vgs vgs on
vgd.vgs_id = vgs.id and vgs.schluessel = ?
inner join tka tka on tka.id = vgd.tka_id
left join (select txt0.* from txt txt0, spr spr
where txt0.spr_id = spr.id and spr.name = ?) txt on tka.txt_id_langname = txt.id
left join (select einh0.* from txt einh0, spr spr
where einh0.spr_id = spr.id and spr.name = ?) einh on tka.txt_id_einheit = einh.id
inner join mtdb mtdb on tka.name = mtdb.tedabattribut
and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
where tka.status = 2
and tka.felddimension = 0
and not exists (select 1 from vgd vgd2
where vgd2.vgs_id = vgd.vgs_id
and vgd2.tka_id = vgd.tka_id
and vgd2.ai_id > vgd.ai_id);
Now as you can see, I have to pass the value to spr.name
twice. I would like to avoid that. The query has been transformed to this for starters
EDIT The query has been rewritten to ANSI style as Leigh Riffel wrote but still no avail.
with spr0 as (select id from spr where name = ?)
select tka.id, tka.name, txt.text as langname, einh.text as einheit, tka.typ,
vgd.a_wert, vgd.i_wert, vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
cross join spr0 spr
inner join vgs vgs on
vgd.vgs_id = vgs.id and vgs.schluessel = ?
inner join tka tka on tka.id = vgd.tka_id
left join (select txt0.* from txt txt0
where txt0.spr_id = spr.id) txt on tka.txt_id_langname = txt.id
left join (select einh0.* from txt einh0
where einh0.spr_id = spr.id) einh on tka.txt_id_einheit = einh.id
inner join mtdb mtdb on tka.name = mtdb.tedabattribut
and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
where tka.status = 2
and tka.felddimension = 0
and not exists (select 1 from vgd vgd2
where vgd2.vgs_id = vgd.vgs_id
and vgd2.tka_id = vgd.tka_id
and vgd2.ai_id > vgd.ai_id);
But SQL Developer is still giving me
ORA-00904: "SPR"."ID": ungültiger Bezeichner
00904. 00000 - "%s: invalid identifier"
Table names have been shortened for brevity.
EDIT 2: I have followed miracle173's advice, reduced the query to a mininum and ran with SQL*Plus
. Here is the output:
select spr.*, txt.*, vgd.*
from vgd vgd
cross join spr spr
inner join tka tka on tka.id = vgd.tka_id
left join (select txt0.* from txt txt0
where txt0.spr_id = spr.id) txt on tka.txt_id_langname = txt.id
where vgd.vgs_id = 32 and spr.name = 'de';
FEHLER in Zeile 6:
ORA-00904: "SPR"."ID": ungültiger Bezeichner
The error is in line 6. So there must be some issue with the cross join and the alias.
Is anyone able to spot the missing link? Where is the flaw in my idea?
Solution: Thanks for Leigh's tip to outfactor table spr
I have a working solution now:
select tka.id, tka.name, txt.text as langname, einh.text as einheit,
mtdb.camosmerkmal as merkmal, tka.typ, vgd.a_wert, vgd.i_wert,
vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
cross join spr spr
inner join vgs vgs on
vgd.vgs_id = vgs.id and vgs.schluessel = ?
inner join tka tka on tka.id = vgd.tka_id and tka.status = 2
and tka.felddimension = 0
left join txt txt on tka.txt_id_langname = txt.id and txt.spr_id = spr.id
left join txt einh on tka.txt_id_einheit = einh.id and einh.spr_id = spr.id
inner join mtdb mtdb on tka.name = mtdb.tedabattribut
and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
where spr.name = ?
and not exists (select 1 from vgd vgd2
where vgd2.vgs_id = vgd.vgs_id
and vgd2.tka_id = vgd.tka_id
and vgd2.ai_id > vgd.ai_id);
Best Answer
There seems to be an issue with the mixture of ANSI and Oracle style joins as well as the referencing of a cross joined table in a subquery. Converting/removing these constructs, you should end up with something like this: