SQL in Oracle, fail in order

jallende

Member
Hi list We have a problem with SQL in Oracle, I sorry by my unknow, but when I tried to run a SQL sentence with just a field like a DCTO in any file f.e. F4201, F4311 etc., grouped by this field and DCTO>= "9R", the result is first "SB", "SH" and then "9R", "9S" etc. Can anyone to explain me why Oracle order first characters an then numbers?

The sentence is:
SELECT CRPDTA.F4201.SHDCTO FROM CRPDTA.F4201 GROUP BY CRPDTA.F4201.SHDCTO
HAVING (((CRPDTA.F4201.SHDCTO)>="9R"))
ORDER BY CRPDTA.F4201.SHDCTO;

The result is:
SHDCTO
--------
SB
SH
9R
9S
9V

Any more information will be highly appreciated, thank a lot list.
javier
 
First, there's no need to use "GROUP/HAVING" in this case: it is replaced with a "WHERE" anyway.
Second, the quotes should be single, not double - it's probably just a typo...

I suspect you may have leading spaces in some cases, try (copy and paste it into SQL*Plus from here):

SELECT '"'||SHDCTO||'"'
FROM CRPDTA.F4201
WHERE SHDCTO >= '9R'
ORDER BY SHDCTO;

If the output is still wrong, copy-and-paste it in your reply to make sure you are not missing spaces or anything in it.

Regards,
Alex.
 
Oracle sort order is set in init.ora and I believe the default sort order is alphanumeric. Check your Oracle documentation.
 
Hi "brother_of_kara", I check this setup with my data base administrator.

Do you believe that the sort order must be Alpha or numeric? Can you send me an init.ora example? I apologize by my ignorance.

Thanks a lot , I appreciate your info.

javier
 
There are a couple of parameters that *can* be set in init.ora file. If they are not set here is the default behavior:


If NLS_SORT is not specified in init.ora, NLS_SORT takes the value of NLS_LANGUAGE.

If NLS_LANGUAGE is not specified in init.ora, NLS_LANGUAGE takes the value of the operating system environment variable NLS_LANG.

So by default, the sort order you got from your SQL statement was most likely determined by your language setting in the operating system.






http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90190/ch1123.htm#REFRN10661
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90190/ch1119.htm#REFRN10657
 
Thaks "brother_of_kara" I solve my problem, I guess. My nls_sort is Spanish I tried with Binary and then it works.

thanks a lot

javier
 
Back
Top