Select only one of three tables

From: <Witold.Iwaniec_at_atl.bluecross.ca>
Date: Tue, 03 Jul 2001 06:41:50 -0700
Message-ID: <F001.0033FE15.20010703061045@fatcity.com>

Hello list

I have a scenario in which I have to check three tables. If there is record in table A, take it otherwise check table B, if there is record in table B, take it otherwise check table C. Let say I am looking for DEPT column and the tables are DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPT column.

While I can check each of the tables in order I would like to do it in one statement. I have tried DECODE but it did not like combination of count and column names - error ORA-00937. To make it simpler here is my query from two tables only:

select decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept   from dept_two d2, dept_three d3
 where d3.emp_id = TESTER_1'
   and d2.emp_id(+) = d3.emp_id

Can someone recommend a solution?

Thanks

Witold

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: Witold.Iwaniec_at_atl.bluecross.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jul 03 2001 - 08:41:50 CDT