Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);
share|improve this question
    
The problem is in SELECT txt0.* FROM txt txt0 WHERE txt0.spr_id = spr0.id where there is no spr0 in the FROM clause. If you check Leigh's answer carefully, there is a line CROSS JOIN spr0 whixh is missing from your version. –  dezso Jan 23 '13 at 9:08
    
@dezso, shame on me. I pasted the wrong query. Now the correct query is in the question but the problem still remains. –  Michael-O Jan 23 '13 at 9:18
    
I think those subselects are unnecessary complications only (could be solved with simply joining those txt tables as you select * from them), but at the moment I don't have the time for proving it. –  dezso Jan 23 '13 at 9:29
1  
Where does the error occurr in your query? I wrote an answer that shows how to find this when using sqlplus. But more important: can you simplify the query and reproduce the error anyway? Simplifying means: remove joins from the where clause, remove expressions from the were-clause (or the whole were-clause if possible), remove fields from the select-list. –  miracle173 Jan 23 '13 at 11:09
    
@miracle173, please see my edit above. Thanks! –  Michael-O Jan 24 '13 at 8:51
add comment

2 Answers 2

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:

WITH spr0 AS (
      SELECT id FROM tka 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
JOIN vgs ON vgd.vgs_id = vgs.id AND vgs.schluessel = ?
JOIN tka tka ON tka.id = vgd.tka_id
   AND tka.txt_id_einheit = einh.id
JOIN mtdb mtdb ON tka.name = mtdb.tedabattribut 
   AND mtdb.tedabattribut IS NOT NULL 
   AND mtdb.baugruppe = ?
LEFT JOIN txt ON txt.spr_id = spr0.id ON tka.txt_id_langname = txt.id
LEFT JOIN txt einh ON einh0.spr_id = spr.id AND spr.name = ? 
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
   );
share|improve this answer
    
Hi Leigh, thanks for your feedback. What problem poses the mising of styles? I have rewritten the query but still getting the same error message. Se my edit of the question. –  Michael-O Jan 23 '13 at 8:54
    
Follow miracle173's instructions in the comment above to diagnose further. There aren't any different columns, so there must be something more complex going on. –  Leigh Riffel Jan 23 '13 at 15:15
    
Done, see question edit! –  Michael-O Jan 24 '13 at 8:45
    
Does it work if you move txt0.spr_id = spr.id out of the subquery and into the ON clause as txt.spr_id = spr.id? –  Leigh Riffel Jan 24 '13 at 13:53
    
Leigh, you are a genius. That did the trick. See edit please and adapt your answer, I will accept it then. –  Michael-O Jan 24 '13 at 15:12
add comment

For analyzing your problem your query

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';

can be simplified further

select *
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

and further

select *
from spr spr
  left join (select txt0.* from txt txt0
    where txt0.spr_id = spr.id) txt

and I think the error will occurr again.

The join syntax is something like

 query_table_expression1 alias1 JOIN query_table_expression2 ON condition

alias1 is known in condition but not in *query_table_expression2*. For me it is not clear what the join (select... clause should do. Perhaps you are mixing up different join styles ( I borrow styles from Leigh Riffel, because I did not know the plural of syntax :-) and you want express something like

select *
from spr, txt 
where txt.spr_id = spr.id

which is the same as

select *
from spr join txt 
on txt.spr_id = spr.id

I avoid to use alias names that are equal to the name of the table that they are aliasing. because it does not make sense to me.

share|improve this answer
    
Thanks for the analysis. Leigh's last tip made it work. Concerning your last remark. I have shortened the table names for the sake of brevity. –  Michael-O Jan 24 '13 at 15:30
    
+1 Excellent analysis. –  Leigh Riffel Jan 24 '13 at 15:39
    
meanwhile if found syntaxes –  miracle173 Jan 24 '13 at 15:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.