to_number question 2004-07-15 - By Jonathan Gennick
Thursday, July 15, 2004, 10:32:41 AM, Stephen.Lee@(protected) (Stephen.Lee@(protected)) 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 (See ORA-00904.ora-code.com): "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@(protected)
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@(protected) 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
