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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Strange ORDER BY behaviour

Re: Strange ORDER BY behaviour

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 30 Aug 2007 17:00:53 +0200
Message-ID: <46d6dbb2$0$242$e4fe514c@news.xs4all.nl>

"Laurenz Albe" <invite_at_spam.to.invalid> schreef in bericht news:1188475490.983462_at_proxy.dienste.wien.at...
> Carlos <miotromailcarlos_at_netscape.net> wrote:
>>> I'm running on 10g Express Edition 10.2.0.1.0
>>>
>>> There is some strange behaviour of the ORDER BY clause in the
>>> following example. I'm executing all SQL via JDBC.
>>>
>>> create table test (foo varchar2(3))
>>>
>>> insert into test values('AAA')
>>> insert into test values('BBB')
>>> insert into test values('CCC')
>>> insert into test values('111')
>>> insert into test values('222')
>>> insert into test values('333')
>>>
>>> select * from test where foo > '200' order by foo
>>>
>>> But what I get is this:
>>> AAA
>>> BBB
>>> CCC
>>> 222
>>> 333
>>>
>>> The selection is as I expected it: the value "111" is not included.
>>> But the ORDER BY seems to use a different charset!?!
>>> As far as I know, the parameters nls_sort and nls_comp are important
>>> for this.
>>>
>>> select * from nls_session_parameters order by parameter
>>>
>>> NLS_COMP BINARY
>>> NLS_SORT GERMAN
>>
>> The answer is NLS_SORT:
>
> ... but NLS_COMP also plays a role:
>
> ALTER SESSION SET NLS_COMP='LINGUISTIC';
> ALTER SESSION SET NLS_SORT='GERMAN';
>
> SELECT foo FROM test WHERE foo > '200' ORDER BY foo;
>
> FOO
> ----------------------------------------
> 222
> 333
>
> If you want comparison and sort to work consistently, the two parameters
> must be set consistently (BINARY with BINARY and LINGUISTIC with GERMAN).
>
> In the latter case you may consider using a linguistic index on
> your text columns:
>
> CREATE INDEX test_foo_ind_de ON test(NLSSORT(foo, 'NLS_SORT = German'));
>
> Yours,
> Laurenz Albe

Which explains my previous post. Thanks!

Shakespeare Received on Thu Aug 30 2007 - 10:00:53 CDT

Original text of this message

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