Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: how to use a cursor.

Re: how to use a cursor.

From: Kyoichi Ozaki <k-ozaki_at_igs.inter-gate.co.jp>
Date: Tue, 10 Oct 2000 13:31:11 +0900
Message-ID: <39E29B8F.F26F580C@igs.inter-gate.co.jp>

thanks for the reply, but it still doesn't work for me. this is the error i get from the php script:

ORA-00907: no right bracket
Query :"select * from (select a.*,rownum r2 from (select x.*,rownum r1 from

     (select songname,label,artist,genre1,genre2,genre3 from wwwsearch where  substr(artist,1,1)='jpchar' OR substr(artistyomi,1,1)='jpchar' order by songname) x) a
where r1>='10') where r2<='20'"

# jpchar is a multibyte char (japanese database)

---
Kyoichi Ozaki
k-ozaki_at_igs.inter-gate.co.jp

Oystein Ornegaard wrote:


> Kyoichi Ozaki <k-ozaki_at_igs.inter-gate.co.jp> wrote:
> : i am working on a web application(cgi) that access an oracle database.
> : but i can't solve this one problem , so please help.

> : i am running a RedHat Linux 6.2 with php-3.0.15 , oracle 8.0.5. and here
> : is the
> : situation:

> : i have a search page, and i need to sort the result of a query into
> : divided pages.
> : and i searched the web to get this solution:

> : > SELECT a,b FROM (SELECT a,b,ROWNUM AS r FROM t WHERE ROWNUM <= 5+10)
> : > WHERE r>=5

> : but with this, i can't use `order by' and also there's no guarantee that
> : i get the same
> : result everytime. so i looked in further to get this:

> : 0 SELECT STATEMENT Optimizer=RULE
> : 1 0 VIEW
> : 2 1 COUNT (STOPKEY)
> : 3 2 TABLE ACCESS (FULL) OF 't'

> : but this example gives me no idea in how to exec the sql statement.
> : (sorry for my
> : ignorance) so could someone give me a more detailed example?

> : like selecting from table t, the fields a,b,c and sorting by b and get
> : 10 rows from the
> : 5th row of the result ?
>
> Hi!
>
> Try this:
>
> 'select * from
> (select a.*,
> rownum r2
> from (select x.*,
> rownum r1 from (select a_field, another_field
> from a_table
> where a_field > another_field
> order by anytingyouwant) x) a
> where r1 >= ' || in_start || ')
> where r2 <= ' || in_number;
>
> The idea is to isolate the innermost select with the order - this way the
> order of the selected rows is not effected when you apply conditions to the
> rownum-columns.
>
> I don't know if this is the most effective solution, but it works. I can't
> help to think that there must be a solution out there with less overhead.
>
> I have found that if you take away one nesting in this statement - that is:
> apply conditions to r1 and r2 on the same level - it will not work. Same
> applies if you try to put the condition on r1 together with the main select.
>
> Do anoyne know of a better solution?
>
> regards
> oystein, norway
Received on Mon Oct 09 2000 - 23:31:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US