The 'where' clause may be applied to many more rows
than appear in the result set (to which the functions
on the selected columns will be applied).
Thus one of the rows that is being encountered does
convert to a valid number.
Basic example follows:
SQL> select to_number(x) from
2 ( select '1' x from dual );
TO_NUMBER(X)
------------
1
SQL> select to_number(x) from
2 ( select '1' x from dual union all select 'X'
from dual )
3 where x = '1'
4 /
TO_NUMBER(X)
------------
1
SQL> select to_number(x) from
2 ( select '1' x from dual union all select 'X'
from dual )
3 where x is not null
4 /
ERROR:
ORA-01722: invalid number
no rows selected
hth
connor
--- John Weatherman
<[EMAIL PROTECTED]> wrote: > Hi all,
>
> I'm writing a package to manipulate a partitioned
> table for the duhvelopers
> and have run into
> a weird query that I can't figure out. I can
> convert a substring to a
> number in a select clause,
> but as soon as I try to use that same number in the
> where clause, the thing
> chokes. Has
> anyone else seen anything like this?
>
> I'm on 9.0.1.3, Solaris 8. The
> buy_price_pkg.cnv_bpt_to_bp_id in the query
> is a custom
> function that returns a number corresponding with
> which partition is
> current, old, next,
> etc.
>
>
> SQL> SELECT to_number(substr(partition_name,5,2)),
> buy_price_pkg.cnv_bpt_to_bp_id(5)
> 2 FROM all_tab_partitions
> 3 WHERE table_name =
> 'BUY_PRICE_PIECE_TYPE_HISTORY'
> 4* and partition_name != 'TYPE01'
> SQL> /
>
> TO_NUMBER(SUBSTR(PARTITION_NAME,5,2))
> BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5)
> -------------------------------------
> ---------------------------------
> 9
> 9
> 10
> 9
> 11
> 9
> 12
> 9
>
> SQL> SELECT to_number(substr(partition_name,5,2)),
> buy_price_pkg.cnv_bpt_to_bp_id(5)
> 2 FROM all_tab_partitions
> 3 WHERE table_name =
> 'BUY_PRICE_PIECE_TYPE_HISTORY'
> 4 and partition_name != 'TYPE01'
> 5* and to_number(substr(partition_name,5,2))
> =
> buy_price_pkg.cnv_bpt_to_bp_id(5)
> SQL> /
> and to_number(substr(partition_name,5,2)) =
> buy_price_pkg.cnv_bpt_to_bp_id(5)
> *
> ERROR at line 5:
> ORA-01722: invalid number
>
> TIA,
>
> John P Weatherman
> Database Administrator
> Replacements Ltd.
>
>
=====
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
