Re: MS-SQL -> Oracle SELECT INTO conversion problem...

I have asked the question because of the following reason:

I have a large select query that returns rows from a table in different sort orders depending on user inputs, and I wonder if there is any way to return just the rows between two specified positions.

I figured that I'm going to either create one temporary table with all the data (1000 records for example), sort the data in the preferred order and use the rownum method to obtain the row range (works fine under T-SQL). Other way is to try an inline view that return the rownumber. I can then restrict the number of fields, e.g.

select empid, rowNumber from emp,
(select empid as id, rownum as rowNumber from emp) x where empid = id
and rowNumber between 2 and 5

The problem is that under Oracle the subquery is not allowed to have an ORDER BY clause (even if it had, rownum reflects row numbers before they were sorted).

Timo Biesenbach <tbiesenb_at_igd.fhg.de> wrote in message news:37971BAE.48F0AF6E_at_igd.fhg.de...
> Hi,
>
> did you try :
> create table new_table as
> select ....
>
> Greetings
> timo
>
> "Slawek L." wrote:
> >
> > Under MS-SQL (T-SQL) Select statement is defined as follows:
> >
> > SELECT select_list
> > [INTO new_table_]
> > ^^^^^^^^^^^^^^^^^^^^^
> > FROM table_source
> > [WHERE search_condition]
> > [GROUP BY group_by_expression]
> > [HAVING search_condition]
> > [ORDER BY order_expression [ASC | DESC] ]
> >
> > Q:How under PL/SQL can one redirect sorted results from SELECT query to
> > another table.
> >
> > Slawek
>
> --
> +---+---+-+-+-+-+
> | | |-+-+-+-+ Fraunhofer-Institut fuer Graphische Datenverarbeitung
> +---+---+-+-+-+-+ Fraunhofer-Institute for Computer Graphics
> | | |-+-+-+-+ Abteilung / Department : Document Imaging
> +---+---+-+-+-+-+
> | __ _ | Timo Biesenbach
> | ! ! _ ! \ | Rundeturmstr. 6 phone ++49 6151 155 578
> | ! !__! !_/ | 64283 Darmstadt fax ++49 6151 155 299
> +---------------+ Germany email timo.biesenbach_at_igd.fhg.de
Received on Thu Jul 22 1999 - 10:35:43 CDT