Re: Coercion issue

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Wed, 04 Sep 2002 09:08:36 -0800
Message-ID: <F001.004C78AB.20020904090836@fatcity.com>

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 <john.weatherman_at_replacements.com> 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: hamcdc_at_yahoo.co.uk

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: ListGuru_at_fatcity.com (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).

Received on Wed Sep 04 2002 - 12:08:36 CDT