Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle: Nested joins/sub-queries: Odd column names and one unexplained error

2629357Apr 14 2014 — edited Apr 14 2014

The following query works fine. It is a series of nested joins to give me a kind of master table:

SELECT *

FROM proj_trainer k

JOIN

(

  SELECT *

  FROM proj_breeder i

  JOIN

  (

  SELECT *

  FROM proj_jockey g

  JOIN

  (

  SELECT *

  FROM proj_horses e

  JOIN

  (

  SELECT *

  FROM proj_results c

  JOIN

  (

  SELECT *

  FROM proj_race_details a

  JOIN proj_meet b

  ON a.meet_id = b.meet_id

  ) d

  ON c.race_id = d.race_id

  ) f

  ON e.horse_id = f.horse_id

  ) h

  ON g.jockey_id = h.jockey_id

  )j

  ON i.breeder_id = j.breeder_id

) l

ON k.trainer_id = l.trainer_id;

This works fine with one odd feature, which isn't my main problem. Some of the columns are return with strange codes such as "QCSJ_C000000001300001". Not sure why, or if this relates to my actual problem.

The real problem is that when I add just one more join sub-query I get:

ORA-00904: "N"."RACE_ID": invalid identifier

Here's the same code with the extra nested block (the one on the very outside)

SELECT *

FROM proj_entry m

JOIN

(

  SELECT *

  FROM proj_trainer k

  JOIN

  (

  SELECT *

  FROM proj_breeder i

  JOIN

  (

  SELECT *

  FROM proj_jockey g

  JOIN

  (

  SELECT *

  FROM proj_horses e

  JOIN

  (

  SELECT *

  FROM proj_results c

  JOIN

  (

  SELECT *

  FROM proj_race_details a

  JOIN proj_meet b

  ON a.meet_id = b.meet_id

  ) d

  ON c.race_id = d.race_id

  ) f

  ON e.horse_id = f.horse_id

  ) h

  ON g.jockey_id = h.jockey_id

  )j

  ON i.breeder_id = j.breeder_id

  ) l

  ON k.trainer_id = l.trainer_id

) n

ON n.race_id = m.race_id AND n.horse_id = m.horse_id;

I felt like I was well over the hill with this one and then the final line went wrong somehow, despite having virtually the same structure as all the previous blocks. I've also used the race_id and horse_id earlier in the code so they do work.

I've also tried this on the last line:

ON n.race_id = m.race_id AND n.horse_id = m.horse_id;

and a few other variations, with brackets etc....

This post has been answered by Frank Kulash on Apr 14 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 12 2014
Added on Apr 14 2014
2 comments
764 views