Re: sql help - group by with subselect

"ed zappulla" <zappullae_at_rcn.com> wrote in message news:9c3u9j$sk$1_at_bob.news.rcn.net...
> Does anyone know why I can't use the country or state in the group by?
>
> SQL>
> select airport_cd,
> (select country.name from country where country_id=airport.country_id)
 as
> country,
> (select state.name from state where state_id=airport.state_id) as state,
> city
> from airport,
> group by airport_cd, country, state
> order by airport_cd
> ;
> group by vendor.name, airport_cd, country, state
> *
> ERROR at line 9:
> ORA-00904: invalid column name
>
>
>
>

Your statement is not correct.
First of all your from clause ends with a , and that would give rise to error messages anyway. Secondly, you try to group by 2 queries, not by individual columns in those queries, so that's not going to work either. What I would do is write this as a simple join, I don't see at all why you would be using subqueries.
Also you'r missing an aggregate function in the select list. It all looks very much looks like your brain was shutdown at the time of writing. I'm sure this isn't your normal way of writing selects.

Regards,

Sybrand Bakker, Oracle DBA Received on Tue Apr 24 2001 - 10:27:31 CDT