Re: SQL statement

Solution within stored procedure for now (I think in 8.1.7 they will fix this flaw; at least there are numerous tar's within Oracle metalink): use native dynamic SQL (execute immediate ...returning ... into ... using ...) -> statement can be tailored at run-time; I think you won't face performance penalties when using dynamic SQL

Example (not tested, currently I'm on 8.1.5 only) using cursor:

create or replace procedure xyz (low_score number, high_score number) is

   sql_stmt varchar2(2000);
   v_player entries_table.player%type;    v_result number;
   type lcl_ref_cur is ref cursor;
   my_ref_curs lcl_ref_cur;
begin

   sql_stmt := 'SELECT.......having...between :low and :high.....'; -- don't terminate the string with ; !!

   open my_ref_curs for sql_stmt using low_score, high_score;    loop

      fetch my_ref_curs into v_player, v_result;
      exit when my_ref_curs%notfound;

• ... processing end loop; close my_ref_curs; end;

Happy fishing

Klaus

Joseph Ranseth <jransethNO_SPAM_at_worldcupfishing.com> schrieb in im Newsbeitrag: RSEm5.1370$ak3.12679_at_news1.mts.net...
> Klaus....Thank you, although this raises a problem that I recall you dealt
> with earlier, it will work fine in sql*plus, but won't compile in a stored
> procedure.
> I can put it in a view, but is there any way I can pass it parameters?
> Otherwise I will be forced to created quite a large number of views to
> handle this.
> Thanks for your help!!!
> JR
> --
> "Klaus Zeuch" <KZeuch_at_nospamhotmail.com> wrote in message
> news:8nf3pn$l4v$18$1_at_news.t-online.com...
> > Assuming the best case (Oracle 8.1.6):
> >
> > select player, sum(actual_points) points
> > from (
> > select player, actual_points,
> > row_number() over (partition by player order by
 actual_points
> > desc) rank
> > from entries_table
> > )
> > where rank < 5 and player in (
> > select player
> > from entries_table
> > group by player
> > having count(*) > 3)
> > group by player
> > having sum(actual_points) between 0 and 50000
> > order by points desc;
> >
> >
> > Klaus
> >
> > "Joseph Ranseth" <jransethNO_SPAM_at_worldcupfishing.com> schrieb im
> > Newsbeitrag news:mjCm5.1355$ak3.12121_at_news1.mts.net...
> > > Hello all,
> > > I need to build a fairly complex SQL statement, and I need some
 help.
> > > I want to pull all the entries for each user from a table, where a
 user
 has
> > > at least 4
> > > records associated to them, and the sum of a field of the top 4
 entries
 are
> > > between 2 parameters. I want to sort the records on the sum of the
 top
 4
> > > records in descending order.
> > > Something like this (although I know this doesn't work)
> > >
> > > SELECT sum(actual_points),
> > > player_name
> > > FROM (SELECT ROWNUM as Rank,
> > > player_name,
> > > actual_points
> > > FROM (SELECT player_name,
> > > actual_points
> > > FROM entries_table
> > > ORDER BY actual_points DESC
> > > )
> > > WHERE rank > 5
> > > )
> > > WHERE sum(actual_points) > 2000
> > > AND sum(actual_points) < 5000
> > > /
> > >
> > > I can't figure out how to make this work....
> > > Thanks in advance for any help!
> > > --
> > > Joseph
> > >
> > >
> > >
> > >
> >
> >
>
> Received on Thu Aug 17 2000 - 09:08:34 CEST