Re: Need oracle expert - SQL

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
> 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 -

sorry, I made a mistake in posting the sql. I'm trying to mimic the 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)