RE: Select only one of three tables
I noticed a select in the code I posted the last select should have been. 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 = 54321. The query will return multiple rows if the person is listed in the applicable table more than once. Ian -Original Message- Sent: Tuesday, July 03, 2001 4:30 PM To: Multiple recipients of list ORACLE-L 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 ---
RE: Select only one of three tables
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 -
Re: Select only one of three tables
[EMAIL PROTECTED] wrote: > > 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 > Witold, select whatever from table_3 where condition and not exists (select 'x' from table_2 where condition union select 'x' from table_1 where condition) union select whatever from table_2 where condition and not exists (select 'x' from table_1 where condition) union select 'x' from table_1 where conditi
RE: Select only one of three tables
This could get ugly. I'm thinking the decode/outer join method won't work because there's no table you can reliably use as a base for the outer join. How about: select emp_id, dept from dept_one union (select emp_id, dept from dept_two minus select emp_id, dept from dept_one) union (select emp_id, dept from dept_three minus (select emp_id, dept from dept_two union select emp_id, dept from dept_one)) / and then go for some coffee if these tables are large at all. Jim Hello listI have a scenario in which I have to check three tables. If there is recordintable A, take it otherwise check table B, if there is record in table B,takeit otherwise check table C. Let say I am looking for DEPT column and thetablesare DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPTcolumn.While I can check each of the tables in order I would like to do it in onestatement. I have tried DECODE but it did not like combination of count andcolumn names - error ORA-00937. To make it simpler here is my query from twotables only:select decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept from dept_two d2, dept_three d3where d3.emp_id = TESTER_1' and d2.emp_id(+) = d3.emp_idCan someone recommend a solution?ThanksWitold-- 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Select only one of three tables
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 b
RE: Select only one of three tables
You mean select * from ( select dept from dept_one union all select dept from dept_two union all select dept from dept_three ) 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 union all select '2', dept from dept_two union all select '3', dept from dept_three 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, Remc
RE: Select only one of three tables
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).
RE: Select only one of three tables
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).
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).
RE: Select only one of three tables
Can't you just check if emp_id is null eg decode(dept_one.dept,null,decode(dept_two.dept,null,dept_three.dept, dept_two.dept), dept_one.dept) dept Iain Nicoll -Original Message- [mailto:[EMAIL PROTECTED]] Sent: 03 July 2001 15:11 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: Nicoll, Iain (Calanais) 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).
Re: Select only one of three tables
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. --- [EMAIL PROTECTED] 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: [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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ 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: [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).
