Re: Strange ORDER BY behaviour

On 30 ago, 10:46, pascal.dull..._at_veda.net wrote:
> Hi!
> 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
>
> One could assume that this would return the following:
> 222
> 333
> AAA
> BBB
> CCC
>
> Because in most ASCII-like charsets the numbers are smaller than the
> letters. 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_CALENDAR GREGORIAN
> NLS_COMP BINARY
> NLS_CURRENCY ?
> NLS_DATE_FORMAT DD.MM.RR
> NLS_DATE_LANGUAGE GERMAN
> NLS_DUAL_CURRENCY ?
> NLS_ISO_CURRENCY GERMANY
> NLS_LANGUAGE GERMAN
> NLS_LENGTH_SEMANTICS BYTE
> NLS_NCHAR_CONV_EXCP FALSE
> NLS_NUMERIC_CHARACTERS ,.
> NLS_SORT GERMAN
> NLS_TERRITORY GERMANY
> NLS_TIME_FORMAT HH24:MI:SSXFF
> NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
> NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
> NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
>
> select * from v$nls_parameters
> NLS_CHARACTERSET WE8MSWIN1252
>
> The value of NLS_COMP is BINARY, and I want it that way. But why does
> the ORDER BY use a different sorting than the WHERE clause?
> In WE8MSWIN1252, are the numbers sorted before or after letters?
>
> Thanks for any help!

The answer is NLS_SORT:

carlos_at_db01.xxxxxx> SHOW PARAMETER NLS

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------
nls_calendar                         string
nls_comp                             string
nls_currency                         string
nls_date_format                      string
nls_date_language                    string
nls_dual_currency                    string
nls_iso_currency                     string
nls_language                         string      SPANISH
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string
nls_sort                             string
nls_territory                        string      SPAIN
nls_time_format                      string
nls_timestamp_format                 string
nls_timestamp_tz_format              string
nls_time_tz_format                   string

carlos_at_db01.xxxxxx> create table test (foo varchar2(3));

Tabla creada.

carlos_at_db01.xxxxxx> insert into test values('AAA');

1 fila creada.

carlos_at_db01.xxxxxx> insert into test values('BBB');

1 fila creada.

carlos_at_db01.xxxxxx> insert into test values('CCC');

1 fila creada.

carlos_at_db01.xxxxxx> insert into test values('111');

1 fila creada.

carlos_at_db01.xxxxxx> insert into test values('222');

1 fila creada.

carlos_at_db01.xxxxxx> insert into test values('333');

1 fila creada.

carlos_at_db01.xxxxxx> COMMIT;

Confirmación terminada.

carlos_at_db01.xxxxxx> select * from test where foo > '200' order by foo ;

FOO

---
AAA
BBB
CCC
222
333

carlos_at_db01.xxxxxx> ALTER SESSION SET NLS_SORT = BINARY;

Sesión modificada.

carlos_at_db01.xxxxxx>  select * from test where foo > '200' order by
foo ;

FOO
---
222
333
AAA
BBB
CCC

carlos_at_db01.xxxxxx>

HTH

Cheers.

Carlos.