Re: Coercion issue

[Igor Neyman]

John,   Oracle will apply both "where" clauses: table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5)  to each row at the same time.   If you want to force oracle to do it sequencially (first - table_name, then - the other one), use in-line view:   SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) FROM   (select partition_name from         all_tab_partitions          WHERE  table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY'         and partition_name != 'TYPE01') WHERE to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) This should do it for you.   Igor Neyman, OCP DBA [EMAIL PROTECTED]   ----- Original Message ----- From: John Weatherman To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 4:59 PM Subject: RE: Coercion issue There are partitions with names that don't have the 5th and 6th characters equal to numbers, but not for this table:   SQL> select partition_name from all_tab_partitions      2   where table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY';   PARTITION_NAME ------------------------------ TYPE01 TYPE09 TYPE10 TYPE11 TYPE12 The query should be restricting things to only those partition names for the one table. John P Weatherman Database Administrator Replacements Ltd. -----Original Message----- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: Coercion issue John,   Do you have other partitions with such names, that 5th and 6th characters are not convertible into numbers? Like: 'TYPEA1'?   Igor Neyman, OCP DBA [EMAIL PROTECTED]   ----- Original Message ----- From: John Weatherman To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 12:43 PM 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.