Re: Ordered Top-N in a subquery

Jeremy Todd wrote:

> I'm an MSSQL guy suddenly thrust into the Oracle world, so bear with
> me.
>
> Suppose I need to get a simple personnel list consisting of name,
> phone, and the person's highest-ranked title (they can have more than
> one). The way it appears I should get the title is:
>
> SELECT *
> FROM (SELECT Title FROM PersonTitle WHERE ID = [x] ORDER BY Rank DESC)
> WHERE rownum = 1
>
> So far, so good. The problem arises when I try to incorporate that
> into the main query:
>
> SELECT
> ID,
> Name,
> Phone,
>
> (SELECT *
> FROM
> (SELECT Title FROM PersonTitle WHERE ID = Person.ID ORDER BY Rank
> DESC)
> WHERE rownum = 1) "Title"
> FROM Person
>
> The error I get is '"Person"."ID": invalid identifier (#904).' I
> gather that the problem is that being embedded in a nested subquery
> severs its access to the fields of the top-level query.
>
> Is there any way around this? This seems like such a common thing
> that I'm sure I must be overlooking something, but Google has been
> less than helpful. This is a data warehouse environment, so all I
> have is SELECT access; I can't create stored procedures or anything.
> Also, unfortunately for me, all the work must be done in one query
> (which will be passed to a third-party tool), so I can't construct the
> result set programmatically, either.
>
> Thanks,
> Jeremy

On first blush what I see that is terribly wrong can be easily solved by using this query:

SELECT keyword
FROM v$reserved_words
WHERE keyword = <word_to_test>;

It is a query you should use frequently.

Both ID and NAME are a keywords in Oracle and should not be used to name columns.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)