Re: Help on expression compare in Oracle

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 20 Jan 2003 13:54:59 -0000
Message-ID: <3e2bffb3$0$240$ed9e5944@reading.news.pipex.net>

"Roby" <roby81it_at_hotmail.com> wrote in message news:78d0e70f.0301200528.46056881_at_posting.google.com...
> And I have this strange situation:
>
> SQL> select * from test where chiave < '0100';
>
> no rows selected
>
> SQL> select * from test where chiave > '0100';
>
> CHIAVE DESCR
> -------------------- ------------------------------
> A1 desc1
> A2 desc2
> A3 desc3
> 0101 desc5
> 0200 desc6
>
> If I decide to order by CHIAVE, why Oracle considers 'A1' lesser than
'0100',
> but when I select values where chiave < '0100', it return no rows ???

You don't actually post the statement with an ORDER BY in it and so not ordering your resultset, you need an order by clause if you wish to order by something.Example shown below.

SQL> create table orderbyeg(id varchar2(4),description varchar2(50));

Table created.

SQL> insert into orderbyeg values('A1','A1 PRODUCTS');

1 row created.

SQL> INSERT INTO ORDERBYEG VALUES ('0101','A2 PRODUCTS'); 1 row created.

SQL> INSERT INTO ORDERBYEG VALUES ('C3','AARDVARKS ''R'' US'); 1 row created.

SQL> COMMIT; Commit complete.

SQL> SELECT * FROM ORDERBYEG WHERE ID > '01'); SELECT * FROM ORDERBYEG WHERE ID > '01')

                                       *

ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> ED
Wrote file afiedt.buf

  1* SELECT * FROM ORDERBYEG WHERE ID > '01' SQL> / ID DESCRIPTION

---- --------------------------------------------------

A1 A1 PRODUCTS
0101 A2 PRODUCTS
C3 AARDVARKS 'R' US SQL> SELECT * FROM ORDERBYEG WHERE ID > '01' ORDER BY ID; ID DESCRIPTION

---- --------------------------------------------------

0101 A2 PRODUCTS
A1 A1 PRODUCTS
C3 AARDVARKS 'R' US If you are using order by and not getting the resuts you intended then that is a different matter.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Mon Jan 20 2003 - 07:54:59 CST