order by problem with datawarehouse rangeset resultset

From: Werner Hofmann <superomega_at_t-online.de>
Date: Thu, 27 Oct 2005 08:07:31 +0200
Message-ID: <djpqup$cho$1@svr7.m-online.net>

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 Received on Thu Oct 27 2005 - 01:07:31 CDT