OraQA

The following is an interesting problem posted on an internet website:

select a.id, b.str
from TARGET_TAB a, lookup_tab b WHERE a.id = b.id

If “TARGET_TAB” Table doesn’t exist in the database, then the alternative table “ALTERNATIVE_TAB” should be used instead.

select a.id, b.str
from ALTERNATIVE_TAB a, lookup_tab b WHERE a.id = b.id

Requirement : Solve this problem by using a single sql query.

create table TARGET_TAB as select level as id from dual connect by level <4;
create table ALTERNATIVE_TAB as select level as id from dual connect by level <6;
create table lookup_tab as select level as id, chr(64+level) str from dual connect by level <6;

————————————————————-SQL Solution———————————————–

WITH XML AS
(SELECT dbms_xmlgen.getxml('SELECT id FROM '||table_name) xml_clob
 FROM user_tables
 WHERE table_name IN
    (SELECT 'TARGET_TAB' FROM dual
      UNION ALL
      SELECT 'ALTERNATIVE_TAB' FROM dual
      WHERE NOT EXISTS (SELECT NULL FROM user_tables WHERE table_name = 'TARGET_TAB')
     )
 ),
 DATA AS
( SELECT ID
  FROM   XML,  XMLTable('ROWSET/ROW'
                         PASSING XMLTYPE(XML.xml_clob)
                         COLUMNS
                         ID NUMBER PATH 'ID')
 )
 SELECT a.ID, str
 FROM DATA a, lookup_tab b
 WHERE a.ID = b.id;

        ID S
---------- -
         1 A
         2 B
         3 C     

drop table target_tab;

Run the same SQL again.

        ID S
---------- -
         1 A
         2 B
         3 C
         4 D
         5 E

Filed under General, SQL, XML. Follow any responses to this entry through this RSS 2.0 feed.