Query question comparing a SYS view and a local table
I have a query that I've been struggling with. It is a (maybe) simple hierarchical query into ALL_SYNONYMS:
SELECT asy.*
FROM ALL_SYNONYMS asy
START WITH ( asy.OWNER = 'PUBLIC') AND
( asy.SYNONYM_NAME = 'MASK_XREF')
CONNECT BY ( asy.SYNONYM_NAME = PRIOR asy.TABLE_NAME) AND
( asy.OWNER = PRIOR asy.TABLE_OWNER);
no rows selected
I know that there is a record in there that should match because this does work:
select * from all_synonyms
where synonym_name like 'MASK%';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------
--
PUBLIC MASK_XREF MANITDBA MASK_XREF