CODEWITHSUNDEEP
sqloracle-database
david
david

Reputation:

syntax error in a join query

I have a query here: For each country in America, give the highest mountain and its height; ordered by height. and tables:

encompasses : country,continent   
geo_mountain: mountain,country  
mountain:name,height

i wrote so:

select co.country,mo.name, max(mo.height) as mheight
from mountain mo,encompasses co
join geo_mountain gmo
on mo.name=gmo.mountain
where gmo.country=any (select country from encompasses where continen='America') 
GROUP BY co.country
order by mheight;

but i get this error in oracle 11g:

Error at Command Line:203 Column:4(second like)
SQL Error: ORA-00904: "MO"."NAME": invalid identifier

Upvotes: 0

Views: 95

Answers (2)

Taryn
Taryn

Reputation: 247680

The problem is that you are mixing JOIN syntax - you are using both explicit and implicit syntax. The explicit JOIN ha a higher precedence so the alias for the tables in the implicit join (mountain, encompasses) are not available to be used. If you want to perform a cross join or cartesian result, then you should use:

select co.country,
  mo.name, 
  max(mo.height) as mheight
from mountain mo
cross join encompasses co
join geo_mountain gmo
  on mo.name=gmo.mountain
where gmo.country=any (select country 
                       from encompasses 
                       where continent ='America') 
GROUP BY co.country, mo.name
order by mheight;

Upvotes: 4

Pete Kandra
Pete Kandra

Reputation:

Try doing your joins on the full table names rather than the alias... on mountain.name = geo_mountain.mountain

Also remember that Oracle is case-sensitive (at least when I last worked with it). You may want to do an upper conversion on your join fields as well.

Upvotes: -1

Related Questions