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