Re: SQL Problem! HELP!

You can't use a subquery as a column. Oracle returns an error because it is expecting a valid column definition or name after the comma.

Your question sounds familiar.... Looks like you might have to either a) create a table that is created as ordered, and then select the first three records using rownum, or b) use PL/SQL.

On Mon, 16 Jun 1997 11:58:56 -0400, Jerry Glass <gglass_at_uspto.gov> wrote:

>I am lookin for a way to solve a problem. I need to find the highest
>values in a list and only return those values (i.e., find the three
>highest values and only return those three rows)
>
>I found this "solution" on the INQUIRY.COM web page, but it does not
>work within Oracle:
>
>
> CREATE TABLE SalesReport (name CHAR(20) NOT NULL,
> territory INTEGER NOT NULL,
> amount DECIMAL (8,2) NOT NULL);
>
> The following query gives the highest SalesReport the rank of 1, the
>next highest the rank of 2 and so forth. The query can handle ties
>within a rank. If the territory has less than (n) members, then it will
>return all the members.
>
> SELECT S0.territory, S0.name, S0.amount,
> (SELECT COUNT(DISTINCT amount)
> FROM SalesReport AS S1
> WHERE (S1.amount >= S0.amount)
> AND (S1.territory = S0.territory)) AS rank
> FROM SalesReport AS S0
> WHERE rank <= 3; -- adjustable parameter
>
>Here's what happens when I run the query:
>
>
> SQL> run
> 1 SELECT S0.territory, S0.name, S0.amount,
> 2 ((SELECT COUNT(DISTINCT amount)
> 3 FROM SalesReport AS S1)
> 4 WHERE (S1.amount >= S0.amount)
> 5 AND (S1.territory = S0.territory))) AS rank
> 6 FROM SalesReport AS S0
> 7* WHERE rank <= 3
> ((SELECT COUNT(DISTINCT amount)
> *
> ERROR at line 2:
> ORA-00936: missing expression
>
>What's wrong with this query.
>
>Any help would be appreciated!
>
>Thanks.
>
>Jerry Glass
Received on Mon Jun 16 1997 - 00:00:00 CDT