Hi Remco
Thanks for the reply. There can be more than one record that I should get. I
guess I could have been clearer - the rule is:
if there is one or more records in dept_one, return all matching records from
dept_one and don't look at other tables.
If there is no record in dept_one, check dept two and if there is one or more
records in dept_two, return the matching records and don't check dept three.
If there are no records in dept_two (so also no records in dept_one) check
dept_three
Originally I thought about decode but it will not work. Ater reading the
responses I think about using union with something like:
select dept from dept_one
where emp_id = TESTER_1'
UNION
select dept from dept_two
where emp_id = TESTER_1'
and not exists (select 1 from dept_one where emp_id = 'TESTER_1')
UNION
select dept from dept_three
where emp_id = TESTER_1'
and not exists (select 1 from dept_one where emp_id = 'TESTER_1')
and not exists (select 1 from dept_two where emp_id = 'TESTER_1')
It should work but I have to look at performance. All I want is save some
network traffic - calls from application to the server
Thanks
Witold
"Daemen, Remco" <[EMAIL PROTECTED]> on 07/03/2001 14:17:41
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: Witold Iwaniec/ATL_BLUECROSS_CA)
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:
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).
