You mean
select * from
(
select dept from dept_one <condition for selecting department>
union all
select dept from dept_two <condition for selecting department>
union all
select dept from dept_three <condition for selecting department>
)
where rownum < 2 ;
I am not sure that you will get records from the table in the same order as
order of select statements. To be sure
select table_number, dept from
(
select '1', dept from dept_one <condition for selecting department>
union all
select '2', dept from dept_two <condition for selecting department>
union all
select '3', dept from dept_three <condition for selecting department>
order by 1
)
where rownum < 2;
Alex Hillman
-----Original Message-----
Sent: Tuesday, July 03, 2001 1:18 PM
To: Multiple recipients of list ORACLE-L
Hi Witold,
Try this:
select * from
(
select dept from dept_one
union all
select dept from dept_two
union all
select dept from dept_three
)
where rownum <=1 ;
HTH, Remco
-----Oorspronkelijk bericht-----
Van: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Verzonden: dinsdag 3 juli 2001 18:37
Aan: Multiple recipients of list ORACLE-L
Onderwerp: RE: Select only one of three tables
Tom
Thanks for the reply.
The UNION would be good if I wanted all dept values from the tables. But the
rule is more complex - if there is eg. one record in table DEPT_ONE, I have
to
get back only this one record even though there may other/more records for
the
same employee in the other tables.
If there is no record in table DEPT_ONE and there is rcord in DEPT_TWO - I
want
back only what is in DEPT_TWO, regardless of what is in DEPT_THREE.
Witold
"Mercadante, Thomas F" <[EMAIL PROTECTED]> on 07/03/2001 13:01:51
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: Witold Iwaniec/ATL_BLUECROSS_CA)
Witold,
have you tried using the UNION operator? like:
select dept
from dept_one
union
select dept
from dept_two
union
select dept
from dept_three
order by 1;
this will give you only one occurrence of the value of dept from all three
tables.
hope this helps
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
[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: Hillman, Alex
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).
