Re: Need oracle expert - SQL
Date: Sun, 2 Mar 2008 19:51:40 -0800 (PST)
Message-ID: <4e585536-a2ed-4489-ba45-d5b16efe8e0c@b1g2000hsg.googlegroups.com>
On Mar 2, 9:37�pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Mar 2, 4:18�pm, marfi95 <marf..._at_yahoo.com> 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
>
> This looks complicated. �It would have been helpful if you had
> included the DDL and DML:
> CREATE TABLE T1(
> � T1JOIN1 NUMBER(10),
> � T1COL1 VARCHAR2(5),
> � T1COL2 VARCHAR2(5),
> � T1COL3 VARCHAR2(5));
>
> T1Join1 T1Col1 � T1Col2 �T1Col3
> ------- -------- ------- -----------
> INSERT INTO T1 VALUES (1,'ABC','123','Test');
> INSERT INTO T1 VALUES (2,'DEF','456','Test2');
> INSERT INTO T1 VALUES (3,'ABC','123','Test3');
> INSERT INTO T1 VALUES (4,'DEF','456','Test4');
>
> CREATE TABLE T2(
> � T1JOIN1 NUMBER(10),
> � T2COL1 VARCHAR2(7),
> � T2COL2 VARCHAR2(5));
>
> INSERT INTO T2 VALUES (1,'Fred','XYZ');
> INSERT INTO T2 VALUES (1,'Charlie','YYY');
> INSERT INTO T2 VALUES (2,'Martha','ABC');
> INSERT INTO T2 VALUES (2,'Jane','ABC');
> INSERT INTO T2 VALUES (3,'Fred','CCC');
> INSERT INTO T2 VALUES (3,'Joan','DDD');
> INSERT INTO T2 VALUES (3,'Jack','EEE');
> INSERT INTO T2 VALUES (3,'Rob','EEE');
> INSERT INTO T2 VALUES (4,'Jane','FFF');
>
> COMMIT;
>
> First, a test to see if we can fix up table T1 so that the rows that
> are related by T1COL are joined together. �We will reference the T1
> table twice, giving it an alias for the second reference:
> SELECT
> � T1.T1JOIN1,
> � T1.T1COL1,
> � T1.T1COL2,
> � T1.T1COL3,
> � T11.T1JOIN1 T11JOIN1,
> � T11.T1COL1 T11COL1,
> � T11.T1COL2 T11COL2,
> � T11.T1COL3 T11COL3
> FROM
> � T1,
> � T1 T11
> WHERE
> � T1.T1COL2=T11.T1COL2
> � AND T1.T1JOIN1<T11.T1JOIN1;
>
> � �T1JOIN1 T1COL T1COL T1COL � T11JOIN1 T11CO T11CO T11CO
> ---------- ----- ----- ----- ---------- ----- ----- -----
> � � � � �1 ABC � 123 � Test � � � � � 3 ABC � 123 � Test3
> � � � � �2 DEF � 456 � Test2 � � � � �4 DEF � 456 � Test4
>
> Now, let's look at table T2. �It would be helpful if there were a
> counter that counts up from 1 for each of the rows with the same value
> for T1JOIN1, and I elected to order the rows by T2COL1:
> SELECT
> � T2.T1JOIN1,
> � T2.T2COL1,
> � T2.T2COL2,
> � ROW_NUMBER() OVER (PARTITION BY T2.T1JOIN1 ORDER BY T2COL1) RN
> FROM
> � T2;
>
> � �T1JOIN1 T2COL1 �T2COL RN
> ---------- ------- ----- --
> � � � � �1 Charlie YYY � �1
> � � � � �1 Fred � �XYZ � �2
> � � � � �2 Jane � �ABC � �1
> � � � � �2 Martha �ABC � �2
> � � � � �3 Fred � �CCC � �1
> � � � � �3 Jack � �EEE � �2
> � � � � �3 Joan � �DDD � �3
> � � � � �3 Rob � � EEE � �4
> � � � � �4 Jane � �FFF � �1
>
> Now we have a bit of a problem in that we need to join these two
> SELECT statements together twice with different join conditions, and
> then outer join the results. �The first of the two joins looks like
> this:
> � SELECT
> � � T1.T1JOIN1,
> � � T1.T1COL1,
> � � T1.T1COL2,
> � � T1.T1COL3,
> � � T1.T11JOIN1,
> � � T1.T11COL1,
> � � T1.T11COL2,
> � � T1.T11COL3,
> � � T2.T2COL1,
> � � T2.T2COL2,
> � � T2.RN
> � FROM
> � � (SELECT
> � � � T1.T1JOIN1,
> � � � T1.T1COL1,
> � � � T1.T1COL2,
> � � � T1.T1COL3,
> � � � T11.T1JOIN1 T11JOIN1,
> � � � T11.T1COL1 T11COL1,
> � � � T11.T1COL2 T11COL2,
> � � � T11.T1COL3 T11COL3
> � � FROM
> � � � T1,
> � � � T1 T11
> � � WHERE
> � � � T1.T1COL2=T11.T1COL2
> � � � AND T1.T1JOIN1<T11.T1JOIN1) T1,
> � � (SELECT
> � � � T2.T1JOIN1,
> � � � T2.T2COL1,
> � � � T2.T2COL2,
> � � � ROW_NUMBER() OVER (PARTITION BY T2.T1JOIN1 ORDER BY T2COL1) RN
> � � FROM
> � � � T2) T2
> � WHERE
> � � T1.T1JOIN1=T2.T1JOIN1;
>
> T1JOIN1 T1COL T1COL T1COL � T11JOIN1 T11CO T11CO T11CO T2COL1 �T2COL
> RN
> ------- ----- ----- ----- ---------- ----- ----- ----- ------- -----
> --
> � � � 1 ABC � 123 � Test � � � � � 3 ABC � 123 � Test3 Fred � �XYZ
> 1
> � � � 1 ABC � 123 � Test � � � � � 3 ABC � 123 � Test3 Charlie YYY
> 2
> � � � 2 DEF � 456 � Test2 � � � � �4 DEF � 456 � Test4 Martha �ABC
> 1
> � � � 2 DEF � 456 � Test2 � � � � �4 DEF � 456 � Test4 Jane � �ABC
> 2
>
> The second time we join the two SELECT statements together, we will
> join with T11.T1JOIN1=T2.T1JOIN1. �We will then full outer join the
> results such that T1COL1 from the one SELECT is joined with T11COL1
> from the second SELECT and RN from the first SELECT is joined to RN
> from the second SELECT:
> SELECT
> � V1.T1JOIN1,
> � V1.T1COL1,
> � V1.T1COL2,
> � V1.T1COL3,
> � V1.T2COL1,
> � V1.T2COL2,
> � V2.T11JOIN1,
> � V2.T11COL1,
> � V2.T11COL2,
> � V2.T11COL3,
> � V2.T2COL1,
> � V2.T2COL2
> FROM
> � (SELECT
> � � T1.T1JOIN1,
> � � T1.T1COL1,
> � � T1.T1COL2,
> � � T1.T1COL3,
> � � T2.T1JOIN1 T2_T1JOIN1,
> � � T2.T2COL1,
> � � T2.T2COL2,
> � � T2.RN
> � FROM
> � � (SELECT
> � � � T1.T1JOIN1,
> � � � T1.T1COL1,
> � � � T1.T1COL2,
> � � � T1.T1COL3,
> � � � T11.T1JOIN1 T11JOIN1,
> � � � T11.T1COL1 T11COL1,
> � � � T11.T1COL2 T11COL2,
> � � � T11.T1COL3 T11COL3
> � � FROM
> � � � T1,
> � � � T1 T11
> � � WHERE
> � � � T1.T1COL2=T11.T1COL2
> � � � AND T1.T1JOIN1<T11.T1JOIN1) T1,
> � � (SELECT
> � � � T2.T1JOIN1,
> � � � T2.T2COL1,
> � � � T2.T2COL2,
> � � � ROW_NUMBER() OVER (PARTITION BY T2.T1JOIN1 ORDER BY T2COL1) RN
> � � FROM
> � � � T2) T2
> � WHERE
> � � T1.T1JOIN1=T2.T1JOIN1) V1
> FULL OUTER JOIN
> � (SELECT
> � � T1.T11JOIN1,
> � � T1.T11COL1,
> � � T1.T11COL2,
> � � T1.T11COL3,
> � � T2.T1JOIN1,
> � � T2.T2COL1,
> � � T2.T2COL2,
> � � T2.RN
> � FROM
> � � (SELECT
> � � � T1.T1JOIN1,
> � � � T1.T1COL1,
> � � � T1.T1COL2,
> � � � T1.T1COL3,
> � � � T11.T1JOIN1 T11JOIN1,
> � � � T11.T1COL1 T11COL1,
> � � � T11.T1COL2 T11COL2,
> � � � T11.T1COL3 T11COL3
> � � FROM
> � � � T1,
> � � � T1 T11
> � � WHERE
> � � � T1.T1COL2=T11.T1COL2
> � � � AND T1.T1JOIN1<T11.T1JOIN1) T1,
> � � (SELECT
> � � � T2.T1JOIN1,
> � � � T2.T2COL1,
> � � � T2.T2COL2,
> � � � ROW_NUMBER() OVER (PARTITION BY T2.T1JOIN1 ORDER BY T2COL1) RN
> � � FROM
> � � � T2) T2
> � WHERE
> � � T1.T11JOIN1=T2.T1JOIN1) V2
> ON
> � V1.T1COL1=V2.T11COL1
> � AND V1.RN=V2.RN;
>
> � �T1JOIN1 T1COL T1COL T1COL T2COL1 �T2COL � T11JOIN1 T11CO T11CO
> T11CO T2COL1 �T2COL
> ---------- ----- ----- ----- ------- ----- ---------- ----- -----
> ----- ------- -----
> � � � � �1 ABC � 123 � Test �Charlie YYY � � � � � �3 ABC � 123
> Test3 Fred � �CCC
> � � � � �1 ABC � 123 � Test �Fred � �XYZ � � � � � �3 ABC � 123
> Test3 Jack � �EEE
> � � � � � � � � � � � � � � � � � � � � � � � � � � 3 ABC � 123
> Test3 Joan � �DDD
> � � � � � � � � � � � � � � � � � � � � � � � � � � 3 ABC � 123
> Test3 Rob � � EEE
> � � � � �2 DEF � 456 � Test2 Jane � �ABC � � � � � �4 DEF � 456
> Test4 Jane � �FFF
> � � � � �2 DEF � 456 � Test2 Martha �ABC
>
> But, we have a problem. �You specified that the first three columns
> could not be NULL, nor could columns 7-9. �Reworking the SQL statement
> using NVL:
> SELECT
> � NVL(V1.T1JOIN1,V2.T1JOIN1) T1JOIN1,
> � NVL(V1.T1COL1,V2.T1COL1) T1COL1,
> � NVL(V1.T1COL2,V2.T1COL2) T1COL2,
> � NVL(V1.T1COL3,V2.T1COL3) T1COL3,
> � V1.T2COL1,
> � V1.T2COL2,
> � NVL(V2.T11JOIN1,V1.T11JOIN1) T11JOIN1,
> � NVL(V2.T11COL1,V1.T11COL1) T11COL1,
> � NVL(V2.T11COL2,V1.T11COL2) T11COL2,
> � NVL(V2.T11COL3,V1.T11COL3) T11COL3,
> � V2.T2COL1,
> � V2.T2COL2
> FROM
> � (SELECT
> � � T1.T1JOIN1,
> � � T1.T1COL1,
> � � T1.T1COL2,
> � � T1.T1COL3,
> � � T1.T11JOIN1,
> � � T1.T11COL1,
> � � T1.T11COL2,
> � � T1.T11COL3,
> � � T2.T2COL1,
> � � T2.T2COL2,
> � � T2.RN
> � FROM
> � � (SELECT
> � � � T1.T1JOIN1,
> � � � T1.T1COL1,
> � � � T1.T1COL2,
> � � � T1.T1COL3,
> � � � T11.T1JOIN1 T11JOIN1,
> � � � T11.T1COL1 T11COL1,
> � � � T11.T1COL2 T11COL2,
> � � � T11.T1COL3 T11COL3
> � � FROM
> � � � T1,
> � � � T1 T11
> � � WHERE
> � � � T1.T1COL2=T11.T1COL2
> � � � AND T1.T1JOIN1<T11.T1JOIN1) T1,
> � � (SELECT
> � � � T2.T1JOIN1,
> � � � T2.T2COL1,
> � � � T2.T2COL2,
> � � � ROW_NUMBER() OVER (PARTITION BY T2.T1JOIN1 ORDER BY T2COL1) RN
> � � FROM
> � � � T2) T2
> � WHERE
> � � T1.T1JOIN1=T2.T1JOIN1) V1
> FULL OUTER JOIN
> � (SELECT
> � � T1.T1JOIN1,
> � � T1.T1COL1,
> � � T1.T1COL2,
> � � T1.T1COL3,
> � � T1.T11JOIN1,
> � � T1.T11COL1,
> � � T1.T11COL2,
> � � T1.T11COL3,
> � � T2.T2COL1,
> � � T2.T2COL2,
> � � T2.RN
> � FROM
> � � (SELECT
> � � � T1.T1JOIN1,
> � � � T1.T1COL1,
> � � � T1.T1COL2,
> � � � T1.T1COL3,
> � � � T11.T1JOIN1 T11JOIN1,
> � � � T11.T1COL1 T11COL1,
> � � � T11.T1COL2 T11COL2,
> � � � T11.T1COL3 T11COL3
> � � FROM
> � � � T1,
> � � � T1 T11
> � � WHERE
> � � � T1.T1COL2=T11.T1COL2
> � � � AND T1.T1JOIN1<T11.T1JOIN1) T1,
> � � (SELECT
> � � � T2.T1JOIN1,
> � � � T2.T2COL1,
> � � � T2.T2COL2,
> � � � ROW_NUMBER() OVER (PARTITION BY T2.T1JOIN1 ORDER BY T2COL1) RN
> � � FROM
> � � � T2) T2
> � WHERE
> � � T1.T11JOIN1=T2.T1JOIN1) V2
> ON
> � V1.T1COL1=V2.T11COL1
> � AND V1.RN=V2.RN;
>
> T1JOIN1 T1COL T1COL T1COL T2COL1 �T2COL � T11JOIN1 T11CO T11CO T11CO
> T2COL1 T2COL
> ------- ----- ----- ----- ------- ----- ---------- ----- ----- -----
> ------------
> � � � 1 ABC � 123 � Test �Charlie YYY � � � � � �3 ABC � 123 � Test3
> Fred � CCC
> � � � 1 ABC � 123 � Test �Fred � �XYZ � � � � � �3 ABC � 123 � Test3
> Jack � EEE
> � � � 1 ABC � 123 � Test � � � � � � � � � � � � 3 ABC � 123 � Test3
> Joan � DDD
> � � � 1 ABC � 123 � Test � � � � � � � � � � � � 3 ABC � 123 � Test3
> Rob � �EEE
> � � � 2 DEF � 456 � Test2 Jane � �ABC � � � � � �4 DEF � 456 � Test4
> Jane � FFF
> � � � 2 DEF � 456 � Test2 Martha �ABC � � � � � �4 DEF � 456 � Test4
>
> Looks like I guessed wrong on the ORDER BY in the ROW_NUMBER
> analytical function, I probably should have ordered by T2COL2 - but
> that should be an easy fix.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Thanks for the reply. I will definately need to study this,as this is very complex. Would it have been easier to do the first part of the checking and store the results in a temporary table, then join that with the additional checking for the outer joins ? Or would it not have mattered ? Received on Sun Mar 02 2008 - 21:51:40 CST