ORACLE sort comp : NLS_SORT nls_comp v$nls_parameters binary
The sort and comp (like between) migth work differently.
-- Two examples:
-- First example:
DROP TABLE TEST;
CREATE TABLE TEST ( entity VARCHAR2(4));
INSERT INTO TEST VALUES('a');
INSERT INTO TEST VALUES('0');
INSERT INTO TEST VALUES('A');
INSERT INTO TEST VALUES('1');
COMMIT;
-- sort and comp are the same:
ALTER SESSION SET nls_sort = BINARY;
ALTER SESSION SET nls_comp=BINARY;
-- 10g feature in 9i default
SELECT * FROM TEST ORDER BY entity;
SELECT * FROM TEST WHERE entity > 'A';
-- sort and comp are different:
ALTER SESSION SET nls_sort = 'German';
SELECT ENTITY from TEST order by ENTITY ;
SELECT entity "This should return 0,1" FROM TEST WHERE entity > 'A';
-- Make the comp behave like the sort:
ALTER SESSION SET nls_comp='ANSI';
SELECT entity "now ok" FROM TEST WHERE entity > 'A';
-- Second example:
DROP TABLE TEST;
CREATE TABLE TEST ( entity VARCHAR2(4));
INSERT INTO TEST VALUES('a');
INSERT INTO TEST VALUES('0');
INSERT INTO TEST VALUES('A');
INSERT INTO TEST VALUES('1');
COMMIT;
CREATE OR REPLACE VIEW yy AS
select min(entity) min_entity,MAX(ENTITY) max_entity
from (SELECT ENTITY from TEST order by ENTITY )
group by floor ( ROWNUM/2 );
ALTER SESSION SET nls_sort = BINARY;
SELECT ENTITY from TEST order by ENTITY ;
select * FROM yy;
SELECT * FROM TEST,yy WHERE entity BETWEEN min_entity AND max_entity ;
ALTER SESSION SET nls_sort = 'German';
SELECT ENTITY from TEST order by ENTITY ;
select * FROM yy;
SELECT * FROM TEST,yy WHERE entity BETWEEN min_entity AND max_entity ;
The output :
SQL>SELECT * FROM TEST ORDER BY entity;
ENTI
----
a
A
0
1
SQL>SELECT * FROM TEST WHERE entity > 'A';
ENTI
----
0
1
SQL>-- sort and comp are different:
SQL>ALTER SESSION SET nls_sort = 'German';
Session altered.
SQL>SELECT ENTITY from TEST order by ENTITY ;
ENTI
----
0
1
A
a
SQL>SELECT entity "This should return 0,1" FROM TEST WHERE entity > 'A';
This
----
a
SQL>-- Make the comp behave like the sort:
SQL>ALTER SESSION SET nls_comp='ANSI';
Session altered.
SQL>SELECT entity "now ok" FROM TEST WHERE entity > 'A';
now
----
0
1
SQL>CREATE OR REPLACE VIEW yy AS
2 select min(entity) min_entity,MAX(ENTITY) max_entity
3 from (SELECT ENTITY from TEST order by ENTITY )
4 group by floor ( ROWNUM/2 );
View created.
SQL>ALTER SESSION SET nls_sort = BINARY;
Session altered.
SQL>SELECT ENTITY from TEST order by ENTITY ;
ENTI
----
0
1
A
a
SQL>select * FROM yy;
MIN_ MAX_
---- ----
1 A
a a
0 0
SQL>SELECT * FROM TEST,yy WHERE entity BETWEEN min_entity AND max_entity ;
ENTI MIN_ MAX_
---- ---- ----
a a a
A 1 A
1 1 A
0 0 0
SQL>ALTER SESSION SET nls_sort = 'German';
Session altered.
SQL>SELECT ENTITY from TEST order by ENTITY ;
ENTI
----
a
A
0
1
SQL>select * FROM yy;
MIN_ MAX_
---- ----
0 A
1 1
a a
SQL>SELECT * FROM TEST,yy WHERE entity BETWEEN min_entity AND max_entity ;
ENTI MIN_ MAX_
---- ---- ----
1 1 1
a a a
SQL>
SQL>
