Re: Advanced Rewrite Challenge

DA Morgan wrote:
>
> Take a look at these two SQL statements:
>
> A) SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id
> = .srvr_id;
>
> B) SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE i.srvr_id
> = s.srvr_id;
>
> They both run. Then add advanced rewrite to the mixture.
>
> BEGIN
> dbms_advanced_rewrite.declare_rewrite_equivalence('UW','SELECT
> DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id =
> i.srvr_id', 'SELECT srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id
> FROM serv_inst i WHERE s.srvr_id = i.srvr_id)', TRUE, 'TEXT_MATCH');
> END;
> /
>
> In TEXT_MATCH mode it works.
> In GENERAL mode it works.
> But choose RECURSIVE and you get the following:
> =======================================================================
> SQL> SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE
> i.srvr_id = s.srvr_id;
> SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE i.srvr_id =
> s.srvr_id
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> =======================================================================
>
> Sorry Oracle but that table does exist. Has anyone seen this before?
> I know what it is doing ... what I'm trying to figure out is why before
> I report it. Thanks.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

if you whack a trace on, does the -942 pop up against a recursive query or against your original SQL ?

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------