RE: Coercion issue

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 04 Sep 2002 12:59:37 -0800
Message-ID: <F001.004C7C4C.20020904125937@fatcity.com>

RE: Coercion issue

It's because of the order that the conditions are checked in the joining of two comparisons with AND.

Example:

-----Original Message-----
From: John Weatherman [mailto:john.weatherman@replacements.com]
Sent: mercredi, 4. septembre 2002 09:44
To: Multiple recipients of list ORACLE-L
Subject: Coercion issue

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.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

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 - 15:59:37 CDT