Something is not right here. Stephen. Here is your query: SLDC> select /*+ NO_MERGE(Z) NO_PUSH_PRED(Z) PUSH_SUBQ(Z) */ distinct SLDC> nbr_cc_fop_name into v_fop from SLDC> (select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low SLDC> from nbr_cc where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z SLDC> where to_number(trim(substr(p_corigid,1,20))) between SLDC> to_number(trim(nbr_cc_no_low)) SLDC> and to_number(trim(nbr_cc_no_high)); How can this even work? Your outer WHERE clause references columns that do not exist in the table returned by your subequery. I built an nbr_cc table and ran your query. Below are the results I get: SQL> select distinct nbr_cc_fop_name into v_fop 2 from ( select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low 3 from nbr_cc 4 where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z 5 where to_number(trim(substr(p_corigid,1,20))) between 6 to_number(trim(nbr_cc_no_low)) 7 and to_number(trim(nbr_cc_no_high)); and to_number(trim(nbr_cc_no_high)) * ERROR at line 7: ORA-00904: "NBR_CC_NO_HIGH": invalid identifier While NBR_CC_NO_HIGH *does* exist in nbr_cc, it does not exist in the table Z that is returned from the subquery. What am I missing here? So far as I can tell, the above query should not even execute for you. SQL> describe nbr_cc Name Null? Type ----------------------------------------- -------- -------------------- NBR_CC_FOP_NAME VARCHAR2(2) NBR_CC_FOP_HIGH VARCHAR2(10) NBR_CC_FOP_LOW VARCHAR2(10) P_CORIGID VARCHAR2(30) NBR_CC_NO_LOW VARCHAR2(20) NBR_CC_NO_HIGH VARCHAR2(20) 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. Wednesday, July 14, 2004, 11:13:22 PM, Stephen.Lee@xxxxxxxx (Stephen.Lee@xxxxxxxx) wrote: >> -----Original Message----- >> I added few hints into your query. SLDC> Hey Moe! It woiks! Nyuk Nyuk Nyuk. SLDC> In case you are wondering, here is what I was working on. Without the hint, SLDC> I get all 'YEEEEEHAAAA'. With the hint, life is good. If there are any of SLDC> you who are dealing with Sardines Oxley stuff -- you know, like what credit SLDC> card numbers you shove into a test and/or development database -- this might SLDC> be of use to you. And the YEEEEHAAAA in the exceptions part should actually SLDC> be p_corigid too (I think). I'm still debating about what to do about the SLDC> when others part. (And the dummy numbers have been changed to protect the SLDC> innocent.) SLDC> -------------------------- SLDC> create or replace function doink(p_corigid in varchar2) return varchar2 as SLDC> v_fop varchar2(10); SLDC> BUM_NUMBER exception; SLDC> pragma exception_init(BUM_NUMBER, -1722); SLDC> begin SLDC> select /*+ NO_MERGE(Z) NO_PUSH_PRED(Z) PUSH_SUBQ(Z) */ distinct SLDC> nbr_cc_fop_name into v_fop from SLDC> (select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low SLDC> from nbr_cc where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z SLDC> where to_number(trim(substr(p_corigid,1,20))) between SLDC> to_number(trim(nbr_cc_no_low)) SLDC> and to_number(trim(nbr_cc_no_high)); SLDC> case v_fop SLDC> when 'AX' then return '123412341234123 '||SUBSTR(p_corigid,21); SLDC> when 'MC' then return '1234123412341234 '||SUBSTR(p_corigid,21); SLDC> when 'VI' then return '2345234523452345 '||SUBSTR(p_corigid,21); SLDC> when 'DS' then return '3456345634563456 '||SUBSTR(p_corigid,21); SLDC> end case; SLDC> exception SLDC> when no_data_found SLDC> then return p_corigid; SLDC> when BUM_NUMBER SLDC> then return 'YEEHAAAAAAAAA'; SLDC> when others SLDC> then return p_corigid; SLDC> end; SLDC> / SLDC> ---------------------------------------------------------------- SLDC> Please see the official ORACLE-L FAQ: http://www.orafaq.com SLDC> ---------------------------------------------------------------- SLDC> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx SLDC> put 'unsubscribe' in the subject line. SLDC> -- SLDC> Archives are at //www.freelists.org/archives/oracle-l/ SLDC> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html SLDC> ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------