Re: Need oracle expert - SQL

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org