topic Re: Oracle 8.1.7 order by in Operating System - HP-UX

Oracle 8.1.7 order by

Jose Mosquera — Fri, 05 Jul 2002 12:45:43 GMT

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

Re: Oracle 8.1.7 order by

Victor Geere — Fri, 05 Jul 2002 13:04:11 GMT

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

Re: Oracle 8.1.7 order by

Jeanine Kone — Fri, 05 Jul 2002 13:20:42 GMT

Re: Oracle 8.1.7 order by

Jose Mosquera — Fri, 05 Jul 2002 13:26:18 GMT

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?

Re: Oracle 8.1.7 order by

Victor Geere — Fri, 05 Jul 2002 13:54:48 GMT

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;

Re: Oracle 8.1.7 order by

Jose Mosquera — Mon, 08 Jul 2002 10:01:53 GMT

Pals,

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

Regards

Re: Oracle 8.1.7 order by

Ian Lochray — Mon, 08 Jul 2002 13:26:09 GMT

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>