difference between order by and between operators
- From: Amihay Gonen <AmihayG@xxxxxxxxx>
- To: "Oracle-L (oracle-l@xxxxxxxxxxxxx)" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 8 May 2005 18:53:56 +0300
Hi ,
we have notice differences between the operator of order by and between.
It seems that nls_sort effect only the order by sort operator , but not the
between.
Is there a nls setting which effect the between operator in a similar way ?
What is the default nls setting for the between ?
Does it an expect behavior ?
Here is an example which show the problem:
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;
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 ;
ALTER SESSION SET nls_sort = BINARY;
SELECT * FROM TEST,yy WHERE entity BETWEEN min_entity AND max_entity ;
The output :
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0
Connected as ps
SQL>
Session altered ( binary sort)
ENTITY
------
0
1
A
a
MIN_ENTITY MAX_ENTITY
---------- ----------
0 0
1 A
a a
Session altered ( german sort)
ENTITY
------
a
A
0
1
MIN_ENTITY MAX_ENTITY
---------- ----------
a a
0 A
1 1
ENTITY MIN_ENTITY MAX_ENTITY
------ ---------- ----------
a a a
0 0 A
A 0 A
1 0 A --------> notice that entity 1
apper twice
1 1 1 ---------> this is a bug .
-------> because if the between
was working like the order by , that 1 0 A - wouldn't be .
Session altered (binary sort)
ENTITY MIN_ENTITY MAX_ENTITY
------ ---------- ----------
0 0 0
A 1 A
1 1 A
a a a
SQL>
Amihay Gonen
DBA,
972-3-9268280
Be there ... -> http://golan/Forum%20DBA/default.aspx
<http://golan/Forum%20DBA/default.aspx>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
