Re[8]: to_number question
- From: Jonathan Gennick <jonathan@xxxxxxxxxxx>
- To: Peter Robson <pgro@xxxxxxxxx>
- Date: Wed, 21 Jul 2004 08:20:53 -0400
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@xxxxxxxxxxx
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@xxxxxxxxxxx and
include the word "subscribe" in either the subject or body.
Wednesday, July 21, 2004, 7:53:10 AM, Peter Robson (pgro@xxxxxxxxx) 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
