I'm assuming there is an emp table as well as the 3 department tables. You can do
this with a case statement.
First some setup
SQL> SELECT * FROM EMPTEST;
EMPNO
---------
12345
67890
54321
SQL> SELECT * FROM DEPT1
2 /
EMPNO DNAME
--------- ----------
12345 STATE
SQL> SELECT * FROM DEPT2
2 /
EMPNO DNAME
--------- ----------
67890 TREASURY
12345 DEFENSE
SQL> SELECT * FROM DEPT3
2 /
EMPNO DNAME
--------- ----------
54321 INTERIOR
12345 JUSTICE
67890 LABOR
SELECT
CASE
WHEN B.EMPNO IS NOT NULL THEN B.DNAME
WHEN C.EMPNO IS NOT NULL THEN C.DNAME
WHEN D.EMPNO IS NOT NULL THEN D.DNAME
ELSE 'NO DEPT'
end DEPT
from emptest a, dept1 b, dept2 c, dept3 d
where a.empno = b.empno(+)
and a.empno = c.empno(+)
and a.empno = d.empno(+)
and (b.empno is not null or c.empno is not null or d.empno is not null)
and a.empno = 12345
/
DEPT
----------
STATE
1 SELECT
2 CASE
3 WHEN B.EMPNO IS NOT NULL THEN B.DNAME
4 WHEN C.EMPNO IS NOT NULL THEN C.DNAME
5 WHEN D.EMPNO IS NOT NULL THEN D.DNAME
6 ELSE 'NO DEPT'
7 end DEPT
8 from emptest a, dept1 b, dept2 c, dept3 d
9 where a.empno = b.empno(+)
10 and a.empno = c.empno(+)
11 and a.empno = d.empno(+)
12 and (b.empno is not null or c.empno is not null or d.empno is
13* and a.empno = 67890
SQL> /
DEPT
----------
TREASURY
SELECT
CASE
WHEN B.EMPNO IS NOT NULL THEN B.DNAME
WHEN C.EMPNO IS NOT NULL THEN C.DNAME
WHEN D.EMPNO IS NOT NULL THEN D.DNAME
ELSE 'NO DEPT'
end DEPT
from emptest a, dept1 b, dept3 c, dept2 d
where a.empno = b.empno(+)
and a.empno = c.empno(+)
and a.empno = d.empno(+)
and (b.empno is not null or c.empno is not null or d.empno is not null)
and a.empno = 54321
/
DEPT
----------
INTERIOR
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 03, 2001 10:11 AM
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Daemen, Remco
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
