RE: Select only one of three tables

From: Nicoll, Iain (Calanais) <iain.nicoll_at_calanais.com>
Date: Tue, 03 Jul 2001 07:58:19 -0700
Message-ID: <F001.00340010.20010703074040@fatcity.com>

Can't you just check if emp_id is null

decode(dept_one.dept,null,decode(dept_two.dept,null,dept_three.dept,

                                                    dept_two.dept),
                          dept_one.dept) dept

Iain Nicoll

-----Original Message-----
[mailto:Witold.Iwaniec_at_atl.bluecross.ca] Sent: 03 July 2001 15:11
To: Multiple recipients of list ORACLE-L

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).
--

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

Author: Nicoll, Iain (Calanais)
INET: iain.nicoll_at_calanais.com

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