 | |
Need oracle expert - SQL
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Need oracle expert - SQL -
03-02-2008
, 03:18 PM
#2
| |||
| |||
|
|
All, I am in need of some expert oracle advice on how to solve the following problem. Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. So, the original record could have more rows (because of table 2) than the duplicate or the other way. I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. I'm open to any ideas, creating temp tables on the fly, etc... One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. I'm hoping there are simpler ways to do this that I'm not thinking of. I'm not an Oracle expert. The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 T1Col1 T1Col2 T1Col3 ------- ---------- ---------- ----------- 1 ABC 123 Test 2 DEF 456 Test2 3 ABC 123 Test3 4 DEF 456 Test4 Table2: =========== T2Join1 T2Col1 T2Col2 -------- --------- ------- 1 Fred XYZ 1 Charlie YYY 2 Martha ABC 2 Jane ABC 3 Fred CCC 3 Joan DDD 3 Jack EEE 3 Rob EEE 4 Jane FFF Result Needed: =============== T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 ---------- ------ ------ ------ ----- ------ ------ ------ ------ ------ ------ ------ 1 ABC 123 Test Fred XYZ 3 ABC 123 Test3 Fred CCC 1 ABC 123 Test Charlie YYY 3 ABC 123 Test3 Joan DDD 1 ABC 123 Test NULL NULL 3 ABC 123 Test3 Jack EEE 1 ABC 123 Test NULL NULL 3 ABC 123 Test3 Rob EEE 2 DEF 456 Test2 Martha ABC 4 DEF 456 Test4 Jane FFF 2 DEF 456 Test2 Jane ABC 4 DEF 456 Test4 NULL NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc |
where appropriate.
Hint 1: In what version of the product (to 3+ decimal places)?
Hint 2: Your column names are a clear violation of basic normalization.
Get help from your instructor on relational table design.
Hint 3: After you have redesigned this mess give it an honest effort
and then, if you still can't figure it out, post your table design:
Give us the DDL. Give us insert statements to create the test data,
and show us your best attempt and explain why it isn't doing what you
think it should do.
Best wishes with your studies.
PS: If you don't want to anger those who might help you ... post to
one, and only one, group.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#3
| |||
| |||
|
|
All, I am in need of some expert oracle advice on how to solve the following problem. Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. So, the original record could have more rows (because of table 2) than the duplicate or the other way. I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. I'm open to any ideas, creating temp tables on the fly, etc... One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. I'm hoping there are simpler ways to do this that I'm not thinking of. I'm not an Oracle expert. The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 T1Col1 T1Col2 T1Col3 ------- ---------- ---------- ----------- 1 ABC 123 Test 2 DEF 456 Test2 3 ABC 123 Test3 4 DEF 456 Test4 Table2: =========== T2Join1 T2Col1 T2Col2 -------- --------- ------- 1 Fred XYZ 1 Charlie YYY 2 Martha ABC 2 Jane ABC 3 Fred CCC 3 Joan DDD 3 Jack EEE 3 Rob EEE 4 Jane FFF Result Needed: =============== T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 ---------- ------ ------ ------ ----- ------ ------ ------ ------ ------ ------ ------ 1 ABC 123 Test Fred XYZ 3 ABC 123 Test3 Fred CCC 1 ABC 123 Test Charlie YYY 3 ABC 123 Test3 Joan DDD 1 ABC 123 Test NULL NULL 3 ABC 123 Test3 Jack EEE 1 ABC 123 Test NULL NULL 3 ABC 123 Test3 Rob EEE 2 DEF 456 Test2 Martha ABC 4 DEF 456 Test4 Jane FFF 2 DEF 456 Test2 Jane ABC 4 DEF 456 Test4 NULL NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc |
where appropriate.
Hint 1: In what version of the product (to 3+ decimal places)?
Hint 2: Your column names are a clear violation of basic normalization.
Get help from your instructor on relational table design.
Hint 3: After you have redesigned this mess give it an honest effort
and then, if you still can't figure it out, post your table design:
Give us the DDL. Give us insert statements to create the test data,
and show us your best attempt and explain why it isn't doing what you
think it should do.
Best wishes with your studies.
PS: If you don't want to anger those who might help you ... post to
one, and only one, group.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#4
| |||
| |||
|
|
All, I am in need of some expert oracle advice on how to solve the following problem. Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. So, the original record could have more rows (because of table 2) than the duplicate or the other way. I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. I'm open to any ideas, creating temp tables on the fly, etc... One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. I'm hoping there are simpler ways to do this that I'm not thinking of. I'm not an Oracle expert. The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 T1Col1 T1Col2 T1Col3 ------- ---------- ---------- ----------- 1 ABC 123 Test 2 DEF 456 Test2 3 ABC 123 Test3 4 DEF 456 Test4 Table2: =========== T2Join1 T2Col1 T2Col2 -------- --------- ------- 1 Fred XYZ 1 Charlie YYY 2 Martha ABC 2 Jane ABC 3 Fred CCC 3 Joan DDD 3 Jack EEE 3 Rob EEE 4 Jane FFF Result Needed: =============== T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 ---------- ------ ------ ------ ----- ------ ------ ------ ------ ------ ------ ------ 1 ABC 123 Test Fred XYZ 3 ABC 123 Test3 Fred CCC 1 ABC 123 Test Charlie YYY 3 ABC 123 Test3 Joan DDD 1 ABC 123 Test NULL NULL 3 ABC 123 Test3 Jack EEE 1 ABC 123 Test NULL NULL 3 ABC 123 Test3 Rob EEE 2 DEF 456 Test2 Martha ABC 4 DEF 456 Test4 Jane FFF 2 DEF 456 Test2 Jane ABC 4 DEF 456 Test4 NULL NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc |
where appropriate.
Hint 1: In what version of the product (to 3+ decimal places)?
Hint 2: Your column names are a clear violation of basic normalization.
Get help from your instructor on relational table design.
Hint 3: After you have redesigned this mess give it an honest effort
and then, if you still can't figure it out, post your table design:
Give us the DDL. Give us insert statements to create the test data,
and show us your best attempt and explain why it isn't doing what you
think it should do.
Best wishes with your studies.
PS: If you don't want to anger those who might help you ... post to
one, and only one, group.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#5
| |||
| |||
|
|
All, I am in need of some expert oracle advice on how to solve the following problem. Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. So, the original record could have more rows (because of table 2) than the duplicate or the other way. I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. I'm open to any ideas, creating temp tables on the fly, etc... One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. I'm hoping there are simpler ways to do this that I'm not thinking of. I'm not an Oracle expert. The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 T1Col1 T1Col2 T1Col3 ------- ---------- ---------- ----------- 1 ABC 123 Test 2 DEF 456 Test2 3 ABC 123 Test3 4 DEF 456 Test4 Table2: =========== T2Join1 T2Col1 T2Col2 -------- --------- ------- 1 Fred XYZ 1 Charlie YYY 2 Martha ABC 2 Jane ABC 3 Fred CCC 3 Joan DDD 3 Jack EEE 3 Rob EEE 4 Jane FFF Result Needed: =============== T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 ---------- ------ ------ ------ ----- ------ ------ ------ ------ ------ ------ ------ 1 ABC 123 Test Fred XYZ 3 ABC 123 Test3 Fred CCC 1 ABC 123 Test Charlie YYY 3 ABC 123 Test3 Joan DDD 1 ABC 123 Test NULL NULL 3 ABC 123 Test3 Jack EEE 1 ABC 123 Test NULL NULL 3 ABC 123 Test3 Rob EEE 2 DEF 456 Test2 Martha ABC 4 DEF 456 Test4 Jane FFF 2 DEF 456 Test2 Jane ABC 4 DEF 456 Test4 NULL NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc |
where appropriate.
Hint 1: In what version of the product (to 3+ decimal places)?
Hint 2: Your column names are a clear violation of basic normalization.
Get help from your instructor on relational table design.
Hint 3: After you have redesigned this mess give it an honest effort
and then, if you still can't figure it out, post your table design:
Give us the DDL. Give us insert statements to create the test data,
and show us your best attempt and explain why it isn't doing what you
think it should do.
Best wishes with your studies.
PS: If you don't want to anger those who might help you ... post to
one, and only one, group.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#6
| |||
| |||
|
|
marfi95 wrote: All, I am in need of some expert oracle advice on how to solve the following problem. *Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. *However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. *The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. *So, the original record could have more rows (because of table 2) than the duplicate or the other way. *I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. *I'm open to any ideas, creating temp tables on the fly, etc... *One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. *I'm hoping there are simpler ways to do this that I'm not thinking of. *I'm not an Oracle expert. *The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. *I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. *The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 * T1Col1 * T1Col2 * T1Col3 ------- * * * ---------- * ---------- * *----------- 1 * * * * * *ABC * * *123 * * * * Test 2 * * * * * *DEF * * *456 * * * * Test2 3 * * * * * *ABC * * *123 * * * * Test3 4 * * * * * *DEF * * *456 * * * * Test4 Table2: =========== T2Join1 T2Col1 * T2Col2 -------- * *--------- * *------- 1 * * * * *Fred * * * XYZ 1 * * * * *Charlie * YYY 2 * * * * *Martha * ABC 2 * * * * *Jane * * *ABC 3 * * * * *Fred * * *CCC 3 * * * * *Joan * * *DDD 3 * * * * *Jack * * *EEE 3 * * * * *Rob * * * EEE 4 * * * * *Jane * * *FFF Result Needed: =============== T1Join1 *T1Col1 *T1Col2 *T1Col3 *T2Col1 *T2Col2 *T1Join1 *T1Col1 T1Col2 T1Col3 * * T2Col1 * * *T2Col2 ---------- * ------ * * *------ * * *------ * * *----- ------ * * *------ * * *------ * * * *------ * *------ ------ * * * * *------ 1 * * * * * ABC * * 123 * * * Test * * *Fred * * *XYZ * * *3 ABC * * * 123 * * * Test3 * * * *Fred * * * *CCC 1 * * * * * ABC * * 123 * * * Test * * *Charlie * YYY * * 3 ABC * * * *123 * * *Test3 * * * *Joan * * * * DDD 1 * * * * * ABC * * 123 * * * Test * * *NULL * * NULL * *3 ABC * * * *123 * * * Test3 * * * *Jack * * ** EEE 1 * * * * * ABC * * 123 * * * Test * * *NULL * * NULL * *3 ABC * * * *123 * * * Test3 * * * *Rob * * * * *EEE 2 * * * * * DEF * * 456 * * * Test2 * * Martha *ABC * * *4 DEF * * * *456 * * * Test4 * * * *Jane * * ** FFF 2 * * * * * DEF * * 456 * * * Test2 * * Jane ** *ABC * * 4 DEF * * * *456 * * * Test4 * * * *NULL * * **NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc You will need to do your own homework ... but we can help with hints where appropriate. Hint 1: In what version of the product (to 3+ decimal places)? Hint 2: Your column names are a clear violation of basic normalization. Get help from your instructor on relational table design. Hint 3: After you have redesigned this mess give it an honest effort and then, if you still can't figure it out, post your table design: Give us the DDL. Give us insert statements to create the test data, and show us your best attempt and explain why it isn't doing what you think it should do. Best wishes with your studies. PS: If you don't want to anger those who might help you ... post to one, and only one, group. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - - Show quoted text - |
school for many years now. This is for my job, I just haven't used
oracle that much. I realize the names are not very good, but I just
made up names and data in an attempt to illustrate the scenario. It's
really a loan/customer scenario. This is for 10g.
I know the join columns between table 1 and 2 should really be the
same name, I just overlooked that. Other than that, nothing wrong
with the column names. The issue I am having is because table 2 can
have multiple rows per table 1 key.
I need to report any duplicate data on Table 1 (along with its
respective Table 2 data). Problem is I need to return both duplicates
on a single row returned to the application. I dont feel like I'm
explaining this very well.
The result row I pass back to my application needs to contain data
from Table 1 and 2 (for both the original and duplicate). If table 2
has has more rows for the duplicate than the original, I need to have
NULLS in the row for the table 2 fields for the original (whereas the
table 2 fields would have the correct data) and vice versa (if table 2
has more rows for the original, then the NULLS need to be in the table
2 fields for the duplicate).
I have already created a view to simplify some of this. the view
is:
create or replace view vw_test
as
select * from table1 t1, table2 t2 where t1.key=t2.key (note I used
"key" as the name of the join column this time)
Then the sql I have is:
select distinct
v1.key, v1.t1col1, v1.t1col2, v1.t1col3, v1.t2col1, v1.t2col2,
v2.key, v2.t1col1, v2.t1col2, v2.t1col3, v2.t2col1, v2.t2col2
from vw_test v1, vvw_test v2
where v1.key <> v2.key
and v1.t1col1 = v2.t1col1
and v1.t1col2 = v2.t1col2
and v1.t2col1 in (select t2col1 from vw_test
where t1col1 = v1.t1col1)
The result I get is if table 2 has more rows for the original than the
original, then the table 2 fields for the duplicate are duplicated on
each row.
I was able to get rid of the duplicates using the analytic LAG
function and decode, but I dont think that rights because it could be
valid for the same column on the previous row to be the same value.
Probably didn't explain very well, but the example I provided explains
it better.
#7
| |||
| |||
|
|
marfi95 wrote: All, I am in need of some expert oracle advice on how to solve the following problem. *Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. *However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. *The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. *So, the original record could have more rows (because of table 2) than the duplicate or the other way. *I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. *I'm open to any ideas, creating temp tables on the fly, etc... *One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. *I'm hoping there are simpler ways to do this that I'm not thinking of. *I'm not an Oracle expert. *The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. *I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. *The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 * T1Col1 * T1Col2 * T1Col3 ------- * * * ---------- * ---------- * *----------- 1 * * * * * *ABC * * *123 * * * * Test 2 * * * * * *DEF * * *456 * * * * Test2 3 * * * * * *ABC * * *123 * * * * Test3 4 * * * * * *DEF * * *456 * * * * Test4 Table2: =========== T2Join1 T2Col1 * T2Col2 -------- * *--------- * *------- 1 * * * * *Fred * * * XYZ 1 * * * * *Charlie * YYY 2 * * * * *Martha * ABC 2 * * * * *Jane * * *ABC 3 * * * * *Fred * * *CCC 3 * * * * *Joan * * *DDD 3 * * * * *Jack * * *EEE 3 * * * * *Rob * * * EEE 4 * * * * *Jane * * *FFF Result Needed: =============== T1Join1 *T1Col1 *T1Col2 *T1Col3 *T2Col1 *T2Col2 *T1Join1 *T1Col1 T1Col2 T1Col3 * * T2Col1 * * *T2Col2 ---------- * ------ * * *------ * * *------ * * *----- ------ * * *------ * * *------ * * * *------ * *------ ------ * * * * *------ 1 * * * * * ABC * * 123 * * * Test * * *Fred * * *XYZ * * *3 ABC * * * 123 * * * Test3 * * * *Fred * * * *CCC 1 * * * * * ABC * * 123 * * * Test * * *Charlie * YYY * * 3 ABC * * * *123 * * *Test3 * * * *Joan * * * * DDD 1 * * * * * ABC * * 123 * * * Test * * *NULL * * NULL * *3 ABC * * * *123 * * * Test3 * * * *Jack * * ** EEE 1 * * * * * ABC * * 123 * * * Test * * *NULL * * NULL * *3 ABC * * * *123 * * * Test3 * * * *Rob * * * * *EEE 2 * * * * * DEF * * 456 * * * Test2 * * Martha *ABC * * *4 DEF * * * *456 * * * Test4 * * * *Jane * * ** FFF 2 * * * * * DEF * * 456 * * * Test2 * * Jane ** *ABC * * 4 DEF * * * *456 * * * Test4 * * * *NULL * * **NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc You will need to do your own homework ... but we can help with hints where appropriate. Hint 1: In what version of the product (to 3+ decimal places)? Hint 2: Your column names are a clear violation of basic normalization. Get help from your instructor on relational table design. Hint 3: After you have redesigned this mess give it an honest effort and then, if you still can't figure it out, post your table design: Give us the DDL. Give us insert statements to create the test data, and show us your best attempt and explain why it isn't doing what you think it should do. Best wishes with your studies. PS: If you don't want to anger those who might help you ... post to one, and only one, group. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - - Show quoted text - |
school for many years now. This is for my job, I just haven't used
oracle that much. I realize the names are not very good, but I just
made up names and data in an attempt to illustrate the scenario. It's
really a loan/customer scenario. This is for 10g.
I know the join columns between table 1 and 2 should really be the
same name, I just overlooked that. Other than that, nothing wrong
with the column names. The issue I am having is because table 2 can
have multiple rows per table 1 key.
I need to report any duplicate data on Table 1 (along with its
respective Table 2 data). Problem is I need to return both duplicates
on a single row returned to the application. I dont feel like I'm
explaining this very well.
The result row I pass back to my application needs to contain data
from Table 1 and 2 (for both the original and duplicate). If table 2
has has more rows for the duplicate than the original, I need to have
NULLS in the row for the table 2 fields for the original (whereas the
table 2 fields would have the correct data) and vice versa (if table 2
has more rows for the original, then the NULLS need to be in the table
2 fields for the duplicate).
I have already created a view to simplify some of this. the view
is:
create or replace view vw_test
as
select * from table1 t1, table2 t2 where t1.key=t2.key (note I used
"key" as the name of the join column this time)
Then the sql I have is:
select distinct
v1.key, v1.t1col1, v1.t1col2, v1.t1col3, v1.t2col1, v1.t2col2,
v2.key, v2.t1col1, v2.t1col2, v2.t1col3, v2.t2col1, v2.t2col2
from vw_test v1, vvw_test v2
where v1.key <> v2.key
and v1.t1col1 = v2.t1col1
and v1.t1col2 = v2.t1col2
and v1.t2col1 in (select t2col1 from vw_test
where t1col1 = v1.t1col1)
The result I get is if table 2 has more rows for the original than the
original, then the table 2 fields for the duplicate are duplicated on
each row.
I was able to get rid of the duplicates using the analytic LAG
function and decode, but I dont think that rights because it could be
valid for the same column on the previous row to be the same value.
Probably didn't explain very well, but the example I provided explains
it better.
#8
| |||
| |||
|
|
marfi95 wrote: All, I am in need of some expert oracle advice on how to solve the following problem. *Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. *However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. *The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. *So, the original record could have more rows (because of table 2) than the duplicate or the other way. *I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. *I'm open to any ideas, creating temp tables on the fly, etc... *One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. *I'm hoping there are simpler ways to do this that I'm not thinking of. *I'm not an Oracle expert. *The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. *I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. *The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 * T1Col1 * T1Col2 * T1Col3 ------- * * * ---------- * ---------- * *----------- 1 * * * * * *ABC * * *123 * * * * Test 2 * * * * * *DEF * * *456 * * * * Test2 3 * * * * * *ABC * * *123 * * * * Test3 4 * * * * * *DEF * * *456 * * * * Test4 Table2: =========== T2Join1 T2Col1 * T2Col2 -------- * *--------- * *------- 1 * * * * *Fred * * * XYZ 1 * * * * *Charlie * YYY 2 * * * * *Martha * ABC 2 * * * * *Jane * * *ABC 3 * * * * *Fred * * *CCC 3 * * * * *Joan * * *DDD 3 * * * * *Jack * * *EEE 3 * * * * *Rob * * * EEE 4 * * * * *Jane * * *FFF Result Needed: =============== T1Join1 *T1Col1 *T1Col2 *T1Col3 *T2Col1 *T2Col2 *T1Join1 *T1Col1 T1Col2 T1Col3 * * T2Col1 * * *T2Col2 ---------- * ------ * * *------ * * *------ * * *----- ------ * * *------ * * *------ * * * *------ * *------ ------ * * * * *------ 1 * * * * * ABC * * 123 * * * Test * * *Fred * * *XYZ * * *3 ABC * * * 123 * * * Test3 * * * *Fred * * * *CCC 1 * * * * * ABC * * 123 * * * Test * * *Charlie * YYY * * 3 ABC * * * *123 * * *Test3 * * * *Joan * * * * DDD 1 * * * * * ABC * * 123 * * * Test * * *NULL * * NULL * *3 ABC * * * *123 * * * Test3 * * * *Jack * * ** EEE 1 * * * * * ABC * * 123 * * * Test * * *NULL * * NULL * *3 ABC * * * *123 * * * Test3 * * * *Rob * * * * *EEE 2 * * * * * DEF * * 456 * * * Test2 * * Martha *ABC * * *4 DEF * * * *456 * * * Test4 * * * *Jane * * ** FFF 2 * * * * * DEF * * 456 * * * Test2 * * Jane ** *ABC * * 4 DEF * * * *456 * * * Test4 * * * *NULL * * **NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc You will need to do your own homework ... but we can help with hints where appropriate. Hint 1: In what version of the product (to 3+ decimal places)? Hint 2: Your column names are a clear violation of basic normalization. Get help from your instructor on relational table design. Hint 3: After you have redesigned this mess give it an honest effort and then, if you still can't figure it out, post your table design: Give us the DDL. Give us insert statements to create the test data, and show us your best attempt and explain why it isn't doing what you think it should do. Best wishes with your studies. PS: If you don't want to anger those who might help you ... post to one, and only one, group. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - - Show quoted text - |
school for many years now. This is for my job, I just haven't used
oracle that much. I realize the names are not very good, but I just
made up names and data in an attempt to illustrate the scenario. It's
really a loan/customer scenario. This is for 10g.
I know the join columns between table 1 and 2 should really be the
same name, I just overlooked that. Other than that, nothing wrong
with the column names. The issue I am having is because table 2 can
have multiple rows per table 1 key.
I need to report any duplicate data on Table 1 (along with its
respective Table 2 data). Problem is I need to return both duplicates
on a single row returned to the application. I dont feel like I'm
explaining this very well.
The result row I pass back to my application needs to contain data
from Table 1 and 2 (for both the original and duplicate). If table 2
has has more rows for the duplicate than the original, I need to have
NULLS in the row for the table 2 fields for the original (whereas the
table 2 fields would have the correct data) and vice versa (if table 2
has more rows for the original, then the NULLS need to be in the table
2 fields for the duplicate).
I have already created a view to simplify some of this. the view
is:
create or replace view vw_test
as
select * from table1 t1, table2 t2 where t1.key=t2.key (note I used
"key" as the name of the join column this time)
Then the sql I have is:
select distinct
v1.key, v1.t1col1, v1.t1col2, v1.t1col3, v1.t2col1, v1.t2col2,
v2.key, v2.t1col1, v2.t1col2, v2.t1col3, v2.t2col1, v2.t2col2
from vw_test v1, vvw_test v2
where v1.key <> v2.key
and v1.t1col1 = v2.t1col1
and v1.t1col2 = v2.t1col2
and v1.t2col1 in (select t2col1 from vw_test
where t1col1 = v1.t1col1)
The result I get is if table 2 has more rows for the original than the
original, then the table 2 fields for the duplicate are duplicated on
each row.
I was able to get rid of the duplicates using the analytic LAG
function and decode, but I dont think that rights because it could be
valid for the same column on the previous row to be the same value.
Probably didn't explain very well, but the example I provided explains
it better.
#9
| |||
| |||
|
|
marfi95 wrote: All, I am in need of some expert oracle advice on how to solve the following problem. *Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. *However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. *The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. *So, the original record could have more rows (because of table 2) than the duplicate or the other way. *I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. *I'm open to any ideas, creating temp tables on the fly, etc... *One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. *I'm hoping there are simpler ways to do this that I'm not thinking of. *I'm not an Oracle expert. *The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. *I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. *The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 * T1Col1 * T1Col2 * T1Col3 ------- * * * ---------- * ---------- * *----------- 1 * * * * * *ABC * * *123 * * * * Test 2 * * * * * *DEF * * *456 * * * * Test2 3 * * * * * *ABC * * *123 * * * * Test3 4 * * * * * *DEF * * *456 * * * * Test4 Table2: =========== T2Join1 T2Col1 * T2Col2 -------- * *--------- * *------- 1 * * * * *Fred * * * XYZ 1 * * * * *Charlie * YYY 2 * * * * *Martha * ABC 2 * * * * *Jane * * *ABC 3 * * * * *Fred * * *CCC 3 * * * * *Joan * * *DDD 3 * * * * *Jack * * *EEE 3 * * * * *Rob * * * EEE 4 * * * * *Jane * * *FFF Result Needed: =============== T1Join1 *T1Col1 *T1Col2 *T1Col3 *T2Col1 *T2Col2 *T1Join1 *T1Col1 T1Col2 T1Col3 * * T2Col1 * * *T2Col2 ---------- * ------ * * *------ * * *------ * * *----- ------ * * *------ * * *------ * * * *------ * *------ ------ * * * * *------ 1 * * * * * ABC * * 123 * * * Test * * *Fred * * *XYZ * * *3 ABC * * * 123 * * * Test3 * * * *Fred * * * *CCC 1 * * * * * ABC * * 123 * * * Test * * *Charlie * YYY * * 3 ABC * * * *123 * * *Test3 * * * *Joan * * * * DDD 1 * * * * * ABC * * 123 * * * Test * * *NULL * * NULL * *3 ABC * * * *123 * * * Test3 * * * *Jack * * ** EEE 1 * * * * * ABC * * 123 * * * Test * * *NULL * * NULL * *3 ABC * * * *123 * * * Test3 * * * *Rob * * * * *EEE 2 * * * * * DEF * * 456 * * * Test2 * * Martha *ABC * * *4 DEF * * * *456 * * * Test4 * * * *Jane * * ** FFF 2 * * * * * DEF * * 456 * * * Test2 * * Jane ** *ABC * * 4 DEF * * * *456 * * * Test4 * * * *NULL * * **NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc You will need to do your own homework ... but we can help with hints where appropriate. Hint 1: In what version of the product (to 3+ decimal places)? Hint 2: Your column names are a clear violation of basic normalization. Get help from your instructor on relational table design. Hint 3: After you have redesigned this mess give it an honest effort and then, if you still can't figure it out, post your table design: Give us the DDL. Give us insert statements to create the test data, and show us your best attempt and explain why it isn't doing what you think it should do. Best wishes with your studies. PS: If you don't want to anger those who might help you ... post to one, and only one, group. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - - Show quoted text - |
school for many years now. This is for my job, I just haven't used
oracle that much. I realize the names are not very good, but I just
made up names and data in an attempt to illustrate the scenario. It's
really a loan/customer scenario. This is for 10g.
I know the join columns between table 1 and 2 should really be the
same name, I just overlooked that. Other than that, nothing wrong
with the column names. The issue I am having is because table 2 can
have multiple rows per table 1 key.
I need to report any duplicate data on Table 1 (along with its
respective Table 2 data). Problem is I need to return both duplicates
on a single row returned to the application. I dont feel like I'm
explaining this very well.
The result row I pass back to my application needs to contain data
from Table 1 and 2 (for both the original and duplicate). If table 2
has has more rows for the duplicate than the original, I need to have
NULLS in the row for the table 2 fields for the original (whereas the
table 2 fields would have the correct data) and vice versa (if table 2
has more rows for the original, then the NULLS need to be in the table
2 fields for the duplicate).
I have already created a view to simplify some of this. the view
is:
create or replace view vw_test
as
select * from table1 t1, table2 t2 where t1.key=t2.key (note I used
"key" as the name of the join column this time)
Then the sql I have is:
select distinct
v1.key, v1.t1col1, v1.t1col2, v1.t1col3, v1.t2col1, v1.t2col2,
v2.key, v2.t1col1, v2.t1col2, v2.t1col3, v2.t2col1, v2.t2col2
from vw_test v1, vvw_test v2
where v1.key <> v2.key
and v1.t1col1 = v2.t1col1
and v1.t1col2 = v2.t1col2
and v1.t2col1 in (select t2col1 from vw_test
where t1col1 = v1.t1col1)
The result I get is if table 2 has more rows for the original than the
original, then the table 2 fields for the duplicate are duplicated on
each row.
I was able to get rid of the duplicates using the analytic LAG
function and decode, but I dont think that rights because it could be
valid for the same column on the previous row to be the same value.
Probably didn't explain very well, but the example I provided explains
it better.
#10
| |||
| |||
|
|
On Mar 2, 5:39*pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote: marfi95 wrote: All, I am in need of some expert oracle advice on how to solve the following problem. *Any ideas would be most helpful. What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. *However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. *The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. *So, the original record could have more rows (because of table 2) than the duplicate or the other way. *I need to report both back to the user. Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. *I'm open to any ideas, creating temp tables on the fly, etc... *One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. *I'm hoping there are simpler ways to do this that I'm not thinking of. *I'm not an Oracle expert. *The other thing I've done is created a view that contains the join of table 1 and 2 already. Hopefully, this will make sense. The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. *I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user. Remember table 1 and 2 are joined before the dup check. *The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example. Table1: ========== T1Join1 * T1Col1 * T1Col2 * T1Col3 ------- * * * ---------- * ---------- * *----------- 1 * * * * * *ABC * * *123 * * * * Test 2 * * * * * *DEF * * *456 * * * * Test2 3 * * * * * *ABC * * *123 * * * * Test3 4 * * * * * *DEF * * *456 * * * * Test4 Table2: =========== T2Join1 T2Col1 * T2Col2 -------- * *--------- * *------- 1 * * * * *Fred * * * XYZ 1 * * * * *Charlie * YYY 2 * * * * *Martha * ABC 2 * * * * *Jane * * *ABC 3 * * * * *Fred * * *CCC 3 * * * * *Joan * * *DDD 3 * * * * *Jack * * *EEE 3 * * * * *Rob * * * EEE 4 * * * * *Jane * * *FFF Result Needed: =============== T1Join1 *T1Col1 *T1Col2 *T1Col3 *T2Col1 *T2Col2 *T1Join1 *T1Col1 T1Col2 T1Col3 * * T2Col1 * * *T2Col2 ---------- * ------ * * *------ * * *------ * * *----- ------ * * *------ * * *------ * * * *------ * * ------ ------ * * * * *------ 1 * * * * * ABC * * 123 * * * Test * * *Fred* * *XYZ * * *3 ABC * * * 123 * * * Test3 * * * *Fred * * * *CCC 1 * * * * * ABC * * 123 * * * Test * * *Charlie * YYY * * 3 ABC * * * *123 * * *Test3 * * * *Joan * * * * DDD 1 * * * * * ABC * * 123 * * * Test * * *NULL* * NULL * *3 ABC * * * *123 * * * Test3 * * * *Jack * * * * EEE 1 * * * * * ABC * * 123 * * * Test * * *NULL* * NULL * *3 ABC * * * *123 * * * Test3 * * * *Rob * * * * *EEE 2 * * * * * DEF * * 456 * * * Test2 * * Martha*ABC * * *4 DEF * * * *456 * * * Test4 * * * *Jane * * * * FFF 2 * * * * * DEF * * 456 * * * Test2 * * Jane * * *ABC * * 4 DEF * * * *456 * * * Test4 * * * *NULL * * * *NULL Hopefully this makes sense and people will take this as a challenge ! Thanks ! Marc You will need to do your own homework ... but we can help with hints where appropriate. Hint 1: In what version of the product (to 3+ decimal places)? Hint 2: Your column names are a clear violation of basic normalization. Get help from your instructor on relational table design. Hint 3: After you have redesigned this mess give it an honest effort and then, if you still can't figure it out, post your table design: Give us the DDL. Give us insert statements to create the test data, and show us your best attempt and explain why it isn't doing what you think it should do. Best wishes with your studies. PS: If you don't want to anger those who might help you ... post to one, and only one, group. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text - - Show quoted text - This is not homework, I wish it was that simple. *I've been out of school for many years now. *This is for my job, I just haven't used oracle that much. *I realize the names are not very good, but I just made up names and data in an attempt to illustrate the scenario. *It's really a loan/customer scenario. *This is for 10g. I know the join columns between table 1 and 2 should really be the same name, I just overlooked that. *Other than that, nothing wrong with the column names. *The issue I am having is because table 2 can have multiple rows per table 1 key. I need to report any duplicate data on Table 1 (along with *its respective Table 2 data). *Problem is I need to return both duplicates on a single row returned to the application. *I dont feel like I'm explaining this very well. The result row I pass back to my application needs to contain data from Table 1 and 2 (for both the original and duplicate). *If table 2 has has more rows for the duplicate than the original, I need to have NULLS in the row for the table 2 fields for the original (whereas the table 2 fields would have the correct data) and vice versa (if table 2 has more rows for the original, then the NULLS need to be in the table 2 fields for the duplicate). I have already created a view to simplify some of this. *the view is: create or replace view vw_test as select * from table1 t1, table2 t2 where t1.key=t2.key *(note I used "key" as the name of the join column this time) Then the sql I have is: select distinct * * *v1.key, v1.t1col1, v1.t1col2, v1.t1col3, v1.t2col1, v1.t2col2, * * *v2.key, v2.t1col1, v2.t1col2, v2.t1col3, v2.t2col1, v2.t2col2 from * *vw_test v1, vvw_test v2 where * * *v1.key <> v2.key and * * * * v1.t1col1 = v2.t1col1 and * * * * v1.t1col2 = v2.t1col2 and * * * * v1.t2col1 in (select t2col1 from vw_test * * * * * * * * * * * * * * * * *where t1col1 = v1.t1col1) The result I get is if table 2 has more rows for the original than the original, then the table 2 fields for the duplicate are duplicated on each row. I was able to get rid of the duplicates using the analytic LAG function and decode, but I dont think that rights because it could be valid for the same column on the previous row to be the same value. Probably didn't explain very well, but the example I provided explains it better.- Hide quoted text - - Show quoted text - |
real app sql with test names.
sql should be:
select distinct
v1.key, v1.t1col1, v1.t1col2, v1.t1col3, v1.t2col1, v1.t2col2,
v2.key, v2.t1col1, v2.t1col2, v2.t1col3, v2.t2col1, v2.t2col2
from vw_test v1, vvw_test v2
where v1.key <> v2.key
and v1.t1col1 = v2.t1col1
and v1.t1col2 = v2.t1col2
and v1.t2col1 in (select t2col1 from vw_test
where key = v2.key)
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.