Re: Help: A difficult request

oh ok here is how you do it in sqlplus its pretty brute force.

insert your counter into a table. then select that table to get your counter each time you query.

so

declare

v_counter number

begin

select stored_counter
into v_counter
from store_table

v_counter := v_counter + 1;

select data
from view
where rownum = v_counter;

end;

not pretty... but will work.

"Cecil D'Souza" <cecildsouza_at_comcast.net> wrote in message news:BhYE8.662$pa.153271_at_bin4.nnrp.aus1.giganews.com...
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3CE42C92.AD07A6CD_at_exesolutions.com...
> > Joe Bayer wrote:
> >
> > > My boss asks me to let user select from a table for only one row each
> > > time, which is very difficult to achieve
> > >
> > > for example
> > >
> > > SQL> select * from test;
> > >
> > > SCHEMA_NAME SCHEMA_VALUE
> > > -------------------- --------------------
> > > A a
> > > B b
> > > C c
> > > D d
> > > E e
> > > F f
> > >
> > > 6 rows selected.
> > >
> > > Each time, we just want user to see one row,
> > > so I created a view
> > >
> > > 1 CREATE OR REPLACE VIEW test_view AS
> > > 3 SELECT *
> > > 4 FROM test
> > > 5* WHERE rownum < 2
> > > SQL> /
> > >
> > > View created.
> > >
> > > but I can only select the first row of the table
> > >
> > > 1 select * from test_view
> > > 2* where schema_name='A'
> > > SQL> /
> > >
> > > SCHEMA_NAME SCHEMA_VALUE
> > > -------------------- --------------------
> > > A a
> > >
> > > 1 select * from test_view
> > > 2* where schema_name='B'
> > > SQL> /
> > >
> > > no rows selected
> > >
> > > can somebody help?
> > >
> > > thanks
> > >
> > >
> > >
> > > --
> > > Sent by joebayerii from hotmail subpart from com
> > > This is a spam protected message. Please answer with reference header.
> > > Posted via http://www.usenet-replayer.com/cgi/content/new
> >
> > There is no way to do what your boss asked. Which once again points out
> > the danger of having people attempt to manage technology who don't
> > understand what they are managing.
> >
> > The only way to achieve this result is either to have them query a
stored
> > procedure (assuming an application interface) or to have them write
their
> > SQL with the following line at the end:
> >
> > AND rownum < 1;
> >
> > And if it is voluntary ... I can assure you it won't happen.
> >
> > But I am fascinated by his request ... Why? Does he think it costs
> > less????
> >
> > Daniel Morgan
> >
> >
>
> Daniel,
>
> You are so right in your judgement about managers. In the last company
that
> I worked as a consultant, we were not allowed to access Metalink. They
> thought it was only for them and I leave you to guess if they know how to
> log in.
>
> Cecil
>
>
Received on Thu May 16 2002 - 20:40:59 CDT