Re[2]: to_number question

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Thu, 15 Jul 2004 10:39:47 -0400
Message-ID: <137987767564.20040715103947@gennick.com>

Thursday, July 15, 2004, 10:32:41 AM, Stephen.Lee_at_DTAG.Com (Stephen.Lee_at_DTAG.Com) 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@gennick.com

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_at_gennick.com 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jul 15 2004 - 09:36:34 CDT