Re: Need oracle expert - SQL
Date: Sun, 2 Mar 2008 16:40:21 -0800 (PST)
On Mar 2, 6:28 pm, marfi95 <marf..._at_yahoo.com> wrote:
> On Mar 2, 5:39 pm, DA Morgan <damor..._at_psoug.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..._at_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
> create or replace view vw_test
> 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 -
sorry, I made a mistake in posting the sql. I'm trying to mimic the real app sql with test names.
sql should be:
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)Received on Sun Mar 02 2008 - 18:40:21 CST