RE: Re[8]: to_number question

From: rob zijlstra <rmsah_at_xs4all.nl>
Date: Wed, 21 Jul 2004 22:23:43 +0200
Message-Id: <200407212023.i6LKNmNS035177@smtp-vbr8.xs4all.nl>

Jonathan,

This really is nicely said:

>>My query would not properly fail

A new optimiser target???

Rob Zijlstra

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Gennick
Sent: woensdag 21 juli 2004 14:21
To: Peter Robson
Cc: Jonathan Gennick
Subject: Re[8]: to_number question

Hello Peter,

Try analyzing the table, which should trigger the cost-based optimizer, which should trigger the error:

analyze table subtest compute statistics;

I had the same problem. My query would not properly fail until I analyzed the table, and then it worked, by failing :-)

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit
http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

Wednesday, July 21, 2004, 7:53:10 AM, Peter Robson (pgro_at_bgs.ac.uk) wrote: PR> Jonathan,

PR> Err, I decided to replicate your query - no error ... odd. Am I PR> missing something? Here below is a log of my session. Its Ora v 8.1.7.

PR> peter
PR> .........

SQL>> create table subtest
PR> 2 (flag char(1),
PR> 3 num varchar2(10));

PR> Table created.

SQL>> insert into subtest values ('A',3);

PR> 1 row created.

SQL>> insert into subtest values ('C',5);

PR> 1 row created.

SQL>> insert into subtest values ('D','alpha');

PR> 1 row created.

SQL>> commit;

PR> Commit complete.

SQL>>
SQL>> select * from subtest;

PR> F NUM
PR> - ----------
PR> A 3
PR> C 5
PR> D alpha

SQL>> select flag,to_number(num) num from subtest PR> 2 where flag in ('A','C');

PR> F        NUM
PR> - ----------
PR> A          3
PR> C          5

SQL>> select * from

PR>   2  (
PR>   3  select flag,to_number(num) num from subtest
PR>   4  where flag in ('A','C')
PR>   5  )
PR>   6  where num > 0;

PR> F        NUM
PR> - ----------
PR> A          3
PR> C          5

PR>      *** No Error Here. ***
     

SQL>>
SQL>> select * from subtest where num > 0; PR> ERROR:
PR> ORA-01722: invalid number

PR> no rows selected

SQL>>

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 21 2004 - 15:23:45 CDT