Re: HELP! Group By will only allow 2 fields.....Why?

From: mAd hAcker <madhackerboy_at_hotmail.com>
Date: 2000/07/06
Message-ID: <Pu595.1907$87.5516@news1.mts.net>#1/1

Klaus, sorry to bother you again, but I came across a wierd one...... I can perform the query from sql*plus without any problems, but when I try to compile it in my stored procedure, it comes back as invalid. It tells me that it is expecting a ',' or 'from' where the '(' is just before the partition.....

Why would it not compile? Is it because I don't have something installed or configured properly?
Klaus, I really appreciate all of the help you have given.....I wish I could repay you. Just let me know if you anything!

Here'e the error message.....

 PLS-00103: Encountered the symbol "(" when expecting one of the

        following:
        , from

And the code..........

14:44:48 SQL> @rankings_proc
14:44:52 SQL> set define off
14:44:52 SQL> create or replace
14:44:52   2  PROCEDURE rankings_proc
14:44:52   3          (in_region      dogfish.states.region_id%type
14:44:52   4          ,in_sort_style  varchar2 default 'leaders')
14:44:52   5  AS
14:44:52   7  --select entries, 1 fish per person, ranked by points
14:44:52   8          CURSOR c_leaders
14:44:52   9          IS
14:44:52  10          SELECT *
14:44:52  11          FROM (  SELECT  ce.pin, ce.date_caught as date_caught,
14:44:52  12                          sp.name as specie_name, st.name as
state_name,
14:44:52  13                          INITCAP(players.first_name ||' '||
players.last_name)
14:44:52  14                          ce.adjusted_points as adjusted_points,
14:44:52  15                          ce.actual_points as actual_points,
14:44:52  16                          row_number() over (partition by ce.pin
order by ce.act
14:44:52  17                  FROM    dogfish.catch_entries ce,
dogfish.species sp,
14:44:52  18                          dogfish.states st, dogfish.players
14:44:52  19                  WHERE   ce.specie_id = sp.specie_id
14:44:52  20                  AND     ce.pin = players.pin
14:44:52  21                  AND     st.state_code = ce.state_caught_in
14:44:52  22                  AND     st.state_code IN (      SELECT
state_code
14:44:52  23                                                  FROM
dogfish.states
14:44:52  24                                                  WHERE
region_id = in_region
14:44:52  25          )
14:44:52  26          WHERE rank_in_partition = 1
14:44:52  27          ;
..............<cut>.................

14:44:52 148 END rankings_proc;
14:44:52 149 /

Warning: Procedure created with compilation errors.

14:44:52 SQL> set define &
14:44:52 SQL> sho err
Errors for PROCEDURE RANKINGS_PROC:

LINE/COL ERROR

-------- -----------------------------------------------------------------
15/22    PLS-00103: Encountered the symbol "(" when expecting one of the
        following:
        , from

--

"Klaus Zeuch" <KZeuch_at_hotmail.com> wrote in message
news:8k2ktc$fo8$15$1_at_news.t-online.com...


> Hi,

Received on Thu Jul 06 2000 - 00:00:00 CDT