Oracle 8.1.7 order by

[Jose Mosquera]

Anybody tell me why Oracle 8.1.7 "order by" sentence sort firts lowercaps and after numbers in a CHAR field?

Please look this:

SQL>
1 select * from jah
2* order by campo asc

C
-
A
a
Z
z
1
7
9

7 filas seleccionadas.


Regards

[Victor Geere]

It's got to do with the language setting NLS that you are using. If you use US7ASCII that won't happen.

select * from test
order by ca asc
--------
1
A
a

select * from test
order by ca desc
------
a
A
1

HTH
Victor

[Jose Mosquera]

In previus machine with HP-10.20 and Oracle 7.3.4 we have defined our environment with NLS_LANG=Spanish_Spain.WE8ISO8859P1, now with Oracle 8.1.7 on HP-UX 11.0 64b using same NLS_LANG definition, "order by" sentence have related troubles. Our language is spanish. Any tip about?

[Victor Geere]

The following statement dynamically changes the linguistic sort sequence to Spanish:

ALTER SESSION
SET NLS_SORT = XSpanish;

Oracle sorts character values based on their position in the Spanish linguistic sort sequence.

alternatively

ALTER SESSION
SET NLS_SORT = ANSI;

[Jose Mosquera]

Pals,

Regarding my last reply (July 05, 14:26) about NLS_LANG, Any suggestion about?

Regards

[Ian Lochray]

Hello,
the order is which the data is displayed is determined by NLS_SORT which can be set at the session level or in the initialisation file. For example ...
SQL> alter session set nls_sort = binary;

Session altered.

SQL>
SQL> select ianchar from ian order by ianchar asc;

I
-
0
9
A
B
a
b

6 rows selected.

SQL> alter session set nls_sort = FRENCH;

Session altered.

SQL> select ianchar from ian order by ianchar asc;

I
-
A
a
B
b
0
9

6 rows selected.

SQL>