Re: order by problem with datawarehouse rangeset resultset

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 27 Oct 2005 15:21:31 +0200
Message-ID: <4360d491$0$21949$9b4e6d93@newsread2.arcor-online.net>

Werner Hofmann schrieb:
> Hallo, I have a problem with this query:
>
> ###############################################
>
> alter session set nls_sort=german
>
> select res.MSGTEXT, vstgr.V_STEUERGRUPPE_KURZ_BEZ,
> vsubsys.V_SUBSYSTEM_KURZ_BEZ, vu.V_UNIT_KURZ_BEZ,
> vutyp.V_UNITTYP_KURZ_BEZ from
> (
> select row_number() over (order by MSGTEXT ASC) as rang, m.* from
> ^^^^^^^^^^^^^^^^^^^^
> OPERATING_MESSAGE m where ((DB_IN_NEW) >= (SYSDATE - 40))
> ) res, v_steuergruppe vstgr, v_subsystem vsubsys, v_units vu, v_unittyp
> vutyp where (vstgr.ID = res.R_V_STEUERGRUPPE_ID and vsubsys.ID =
> res.R_V_SUBSYSTEM_ID and vu.ID = res.R_V_UNITS_ID and vutyp.ID =
> res.R_V_UNITTYP_ID) and (rang between 1 and 1)
> ^^^^^^^^^^^^^^^^^^^^
> order by MSGTEXT ASC
>
> ###############################################
>
> in res.MSGTEXT are lines like:
>
> 'Das ist eine Testmeldung'
> 'Kommentarlos gestrichen'
> 'ampel ausgefallen'
> 'der detektor xxx ist ausgefallen'
>
> ###############################################
>
> I need a rangeset of results because I have very much datasets inside
> the database but the user should not see every line. Also I don't want
> to send every line to the client-application, because of memory.
> In my results I need for example the lines 3000 to 3030.
>
> with the statement above I would expect the result
>
> 'ampel ausgefallen'
>
> but the database sends me
>
> 'Das ist eine Testmeldung'
> which is not correct because the ordering is not 'german' as I have set
> with the alter statement.
>
> At the moment it seems, that the result is only correct, if I want to
> have all lines of the table OPERATING_MESSAGE.
>
> Is there a way to use a correct oracle-SQL-statement, or do I have to
> run through all lines and select the wanted lines out of the resultset
> by myself? (I have the fear, that this would take a lot of time)
>
> Thanks,
> Werner

Could you please post your OS and Oracle Version, on my 10gR2 on Linux i can definitely not reproduce your behaviour:

oracle_at_col-fc1-04:~ >sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 27 12:11:48 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> create table test(text varchar2(2000));

Table created.

SQL> insert into test values('Das ist eine Testmeldung');

1 row created.

SQL> insert into test values('Kommentarlos gestrichen');

1 row created.

SQL> insert into test values('ampel ausgefallen');

1 row created.

SQL> insert into test values('der detektor xxx ist ausgefallen');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test order by 1;

TEXT

Das ist eine Testmeldung
Kommentarlos gestrichen
ampel ausgefallen
der detektor xxx ist ausgefallen

SQL> alter session set nls_sort=german ;

Session altered.

SQL> select * from test order by 1;

TEXT

ampel ausgefallen
Das ist eine Testmeldung
der detektor xxx ist ausgefallen
Kommentarlos gestrichen

SQL> col text for a25
SQL> select row_number() over(order by text asc) rn,text from test;

RN TEXT

---------- -------------------------
          1 ampel ausgefallen
          2 Das ist eine Testmeldung
          3 der detektor xxx ist ausg
            efallen

          4 Kommentarlos gestrichen

SQL> select * from

2 (select row_number() over(order by text asc) rn,text from test) 3 where rn between 1 and 1;

RN TEXT

---------- -------------------------
          1 ampel ausgefallen

SQL> Best regards

Maxim Received on Thu Oct 27 2005 - 08:21:31 CDT