[Hint] Order By in different NLS_LANG

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Sat, 15 May 2004 7:14 pm

We know that changing the NLS_LANG of oracle, he assumes different behaviors. (For example, change the date format, change the Decimal point or Decimal point, etc.) But one thing that sometimes goes unnoticed is that the NLS_LANG can also change the order of columns: Example: (Note the difference)
Code: Select all
SQL> SELECT * FROM A ORDER BY 1;

F
-
1
5
A
B
C

SQL> ALTER SESSION SET NLS_LANGUAGE = 'PORTUGUESE';

Session altered.

SQL> SELECT * FROM A ORDER BY 1;

F
-
A
B
C
1
5
as can be seen, changed from AMERICAN to ENGLISH and the order changed. In the first case, the numbers came in first. In the second, the numbers were finally. How can we be sure that a column always will obey the same order?

[size = 150]1 solution [/size] can create a small function that indicates whether the field is numeric or not. Hence, we ordered first by the result of this function. Here goes:
Code: Select all
SQL> create or replace Function f_nro ( p_numero in varchar2 ) return number
  2  as
  3    l_numero number;
  4  begin
  5    l_numero := to_number(p_numero);
  6    return 1;
  7  exception when others then return 0;
  8  end;
  9 
10 
11  /

Function created.

SQL> select f_nro('1234') from dual;

F_NRO('1234')
-------------
              1


SQL> select f_nro('1234a') from dual;

F_NRO('1234A')
--------------
             0

SQL>
see ... she returns ZERO if not a number! Then we can sort this way:
Code: Select all
SQL> ALTER SESSION SET NLS_LANGUAGE = 'PORTUGUESE';

Session altered.

SQL>  SELECT * FROM A ORDER BY 1;

F
-
A
B
C
1
5

SQL> SELECT * FROM A ORDER BY f_nro(FIELD) desc, field;

F
-
5
1
A
B
C

SQL>
Voalá!!!


[size = 150]2 solution [/size] you can use the DUMP function. Example:
Code: Select all
SQL> select dump('A') from dual;

DUMP('A')
----------------
Typ=96 Len=1: 65

SQL> SELECT * FROM A ORDER BY dump(FIELD);

F
-
1
5
A
B
C
good luck! If you have other ... feel free to post them here!
dr_gori
Location: Portland, OR USA


  • See also
    Replies
    Views
    Last Post

    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 3 guests