If the row is only found in 1 table then you can use
union :
select dept from dept1 where condition
union
select dept from dept2 where condition
union
select dept from dept3 where condition
You can aldo hide the structure behind a view.
- Witold.Iwaniec_at_atl.bluecross.ca a écrit : >
>
> 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).
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
Do You Yahoo!? -- Pour faire vos courses sur le Net,
Yahoo! Shopping :
http://fr.shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com
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 - 09:53:57 CDT
