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

I found a workaround (requires at least on platform windows nt oracle version 8.1.6.1.x; definitely doesn't work with 8.1.6.0.x): place the select-statement with analytical functions in a view and use that view in your pl/sql code (with 8.1.6.0.x you are facing runtime-errors)

hth

Klaus

"Klaus Zeuch" <KZeuch_at_hotmail.com> schrieb im Newsbeitrag news:8k2t3d$s77$18$1_at_news.t-online.com...
> Hello,
>
> I'm facing the same problems (see my posting at 23:14). I searched Oracle
> metalink but found nothing about that.
>
> Sorry
>
> Klaus
>
> "mAd hAcker" <madhackerboy_at_hotmail.com> schrieb im Newsbeitrag
> news:Pu595.1907$87.5516_at_news1.mts.net...
> > 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 Sat Jul 08 2000 - 00:00:00 CDT