RE: Select only one of three tables

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Tue, 03 Jul 2001 15:25:27 -0700
Message-ID: <F001.00340C0A.20010703153022@fatcity.com>

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
ian_at_slac.stanford.edu

[mailto:Witold.Iwaniec_at_atl.bluecross.ca] 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: 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: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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: 
  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: Daemen, Remco
  INET: R.Daemen_at_facent.nl

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: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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 - 17:25:27 CDT