Re[2]: to_number question

  • From: Jonathan Gennick <jonathan@xxxxxxxxxxx>
  • To: "Stephen.Lee@xxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 15 Jul 2004 10:39:47 -0400

Thursday, July 15, 2004, 10:32:41 AM, Stephen.Lee@xxxxxxxx 
(Stephen.Lee@xxxxxxxx) wrote:
>> because when I tried to execute it I
>> received an error that made me realize that
>> his query should not work at all.

SLDC> I swear I can hear the Twilight Zone theme song playing.

No, seriously, unless somehow I pulled the wrong query from
your earlier email, it should not execute at all:

select /*+ NO_MERGE(Z) NO_PUSH_PRED(Z) PUSH_SUBQ(Z) */
       distinct nbr_cc_fop_name into v_fop
from ( select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low
       from nbr_cc
       where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z
where to_number(trim(substr(p_corigid,1,20))) between
         to_number(trim(nbr_cc_no_low))
         and to_number(trim(nbr_cc_no_high));

Your subquery returns three columns: nbr_cc_fop_name,
nbr_cc_fop_high, and nbr_cc_fop_low. Your main query
references columns like nbr_cc_no_low and nbr_cc_no_high.
These columns do not exist in the results from the subquery.
When I execute the above query, I receive the following
error:

ERROR at line 8:
ORA-00904: "NBR_CC_NO_HIGH": invalid identifier

This error is what I'd expect.

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.

----------------------------------------------------------------
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: