This could get ugly. I'm thinking the decode/outer join method won't
work because there's no table you can reliably use as a base for the outer
join. How about:
select emp_id, dept from dept_one
union
(select emp_id, dept from dept_two minus select emp_id, dept from
dept_one)
union
(select emp_id, dept from dept_three minus
(select emp_id, dept from
dept_two union select emp_id, dept from dept_one))
/
and then go for some coffee if these tables are large at all.
Jim
Hello listI have a scenario in which I have to check three
tables. If there is recordintable A, take it otherwise check table B, if
there is record in table B,takeit otherwise check table C. Let say I am
looking for DEPT column and thetablesare DEPT_ONE, DEPT_TWO, and
DEPT_THREE. At the end I need only one DEPTcolumn.While I can check
each of the tables in order I would like to do it in onestatement. I have
tried DECODE but it did not like combination of count andcolumn names -
error ORA-00937. To make it simpler here is my query from twotables
only:select decode (count(d2.emp_id), 0, d3.dept, d2.dept)
dept from dept_two d2, dept_three d3where d3.emp_id =
TESTER_1' and d2.emp_id(+) = d3.emp_idCan someone
recommend a solution?ThanksWitold-- Please see
the official ORACLE-L FAQ: <A
href="http://www.orafaq.com">http://www.orafaq.com-- Author:
INET: Witold.Iwaniec_at_atl.bluecross.caFat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051San
Diego, California -- Public Internet
access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso 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.comFat City
Network Services -- (858) 538-5051 FAX: (858)
538-5051San Diego, California --
Public Internet access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).
Received on Tue Jul 03 2001 - 13:30:17 CDT
