Received: (qmail 22009 invoked from network); 9 Sep 2009 08:19:48 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 9 Sep 2009 08:19:38 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2B28BCAE076; Wed, 9 Sep 2009 09:18:34 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id oSGCpiPcCzWf; Wed, 9 Sep 2009 09:18:33 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3E7C8CAE00A; Wed, 9 Sep 2009 09:17:56 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 09 Sep 2009 09:17:15 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E05D8CADFEA for ; Wed, 9 Sep 2009 09:17:14 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id L5HVvBuj0Pmm for ; Wed, 9 Sep 2009 09:17:14 -0400 (EDT) Received: from mx02.filter.theplanet.com (mx02.filter.theplanet.com [216.185.111.116]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B5B02CADF52 for ; Wed, 9 Sep 2009 09:17:13 -0400 (EDT) Delivery-date: Wed, 09 Sep 2009 08:17:13 -0500 Received: from mail.hotsos.com ([69.93.176.68] helo=hotsos.com) by mx02.filter.theplanet.com (envelope-from ric.van.dyke@hotsos.com) with esmtp (Exim) id 1MlN2r-000Jog-By for oracle-l@freelists.org; Wed, 09 Sep 2009 08:17:11 -0500 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CA314F.D52145F0" Subject: RE: Operator and condition precedence Date: Wed, 9 Sep 2009 08:17:08 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Operator and condition precedence References: <26fdee6e0909081635h4b1a1562wbf5d51e3d5635461@mail.gmail.com> <26fdee6e0909082219r3dc6efb0lfaf5a4db0f3a3fdb@mail.gmail.com> From: "Ric Van Dyke" To: "Michael Moore" Cc: , "Oracle L" X-Spam-Level: / X-Spam-Score: 0.5 X-archive-position: 20667 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: ric.van.dyke@hotsos.com Precedence: normal Reply-to: ric.van.dyke@hotsos.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l ------_=_NextPart_001_01CA314F.D52145F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Right you are: =20 SQL> select * from dual 2 where 'xyz'=3D'' and to_number('abc') > 0; where 'xyz'=3D'' and to_number('abc') > 0 * ERROR at line 2: ORA-01722: invalid number =20 SQL> SQL> SQL> explain plan for 2 select * from dual 3 where 'xyz'=3D'' and to_number('abc') > 0; SQL> select * from table(dbms_xplan.display); =20 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------ ---- ------------------------------------------------------------------------ ---- ------------------------------------ Plan hash value: 3752461848 =20 ------------------------------------------------------------------------ --- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------ --- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------ --- =20 Predicate Information (identified by operation id): --------------------------------------------------- =20 1 - filter(TO_NUMBER('abc')>0 AND 'xyz'=3D'') =20 ----------------------- Ric Van Dyke Hotsos Enterprises ----------------------- =20 Hotsos Symposium=20 March 7 - 11, 2010=20 Be there. =20 =20 ________________________________ From: Michael Moore [mailto:michaeljmoore@gmail.com]=20 Sent: Tuesday, September 08, 2009 10:19 PM To: Ric Van Dyke Cc: rjoralist@society.servebeer.com; Oracle L Subject: Re: Operator and condition precedence =20 Ric, On my system =20 =09 select * from dual where 'xyz'>'' and to_number('abc') > 0; throws a numeric error=20 =20 =09 Mike =20 =20 =20 ------_=_NextPart_001_01CA314F.D52145F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Right you = are:

 

SQL> = select * from dual

  = 2  where 'xyz'=3D'' and to_number('abc') > 0;

where = 'xyz'=3D'' and to_number('abc') > 0

         &n= bsp;           &nb= sp;       *

ERROR at = line 2:

ORA-01722: invalid number

 

SQL>

SQL>

SQL> = explain plan for

  = 2  select * from dual

  = 3  where 'xyz'=3D'' and to_number('abc') > 0;

SQL> = select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------= --------------------

--------------------------------------------------------= --------------------

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

Plan = hash value: 3752461848

 

--------------------------------------------------------= -------------------

| = Id  | Operation          | Name | = Rows  | Bytes | Cost (%CPU)| Time     = |

--------------------------------------------------------= -------------------

|   0 | SELECT STATEMENT   |      = |     1 |     2 = |     2   (0)| 00:00:01 = |

|*  = 1 |  FILTER            = |      |       = |       = |            = |          = |

|   2 |   TABLE ACCESS FULL| DUAL |     1 |     = 2 |     2   (0)| 00:00:01 = |

--------------------------------------------------------= -------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------=

 

   1 - filter(TO_NUMBER('abc')>0 AND = 'xyz'=3D'')

 

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

Ric Van Dyke

Hotsos = Enterprises

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

 

=

Hotsos Symposium =

March 7 – 11, 2010 =

Be = there.

 

=

 

=

From: = Michael Moore [mailto:michaeljmoore@gmail.com]
Sent: Tuesday, September = 08, 2009 10:19 PM
To: Ric Van Dyke
Cc: = rjoralist@society.servebeer.com; Oracle L
Subject: Re: Operator and condition precedence

 

Ric,
On my system
 


select * from dual
where 'xyz'>'' and to_number('abc') > = 0;

throws a numeric error

 


Mike

 

 

 

------_=_NextPart_001_01CA314F.D52145F0-- -- http://www.freelists.org/webpage/oracle-l