Home » SQL & PL/SQL » SQL & PL/SQL » Need help on SQL join. (Oracle 11gR2)
Need help on SQL join. [message #584439] Tue, 14 May 2013 06:33 Go to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

I have to compare two tables and return matching data based on sone condition. These two tables contains similar data.

Table SOURCE_CONS contains the metadata of the constraints in source schema and TARGET_CONS contains the metadata of the constraints in targer schema.

Scripts to help you:
CREATE TABLE SOURCE_CONS
  (
    "CONSTRAINT_NAME"   VARCHAR2(30 BYTE),
    "CONSTRAINT_TYPE"   VARCHAR2(1 BYTE),
    "TABLE_NAME"        VARCHAR2(30 BYTE),
    "R_CONSTRAINT_NAME" VARCHAR2(30 BYTE),
    "STATUS"            VARCHAR2(8 BYTE),
    "OBJECT_TEXT"       VARCHAR2(2000 BYTE),
    "COLUMN_NAME"       VARCHAR2(30 BYTE),
    "ACTION"            VARCHAR2(30 BYTE)
  );
  
Insert into SOURCE_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('FK3','R','TF2','PK3','ENABLED',null,'COL4',null);
Insert into SOURCE_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('FK3','R','TF2','PK3','ENABLED',null,'COL5',null);
Insert into SOURCE_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK1','P','T1',null,'ENABLED',null,'COL3',null);
Insert into SOURCE_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK3','P','T2',null,'ENABLED',null,'COL4',null);
Insert into SOURCE_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK3','P','T2',null,'ENABLED',null,'COL5',null);
Insert into SOURCE_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK7','P','T5',null,'ENABLED',null,'C1',null);
Insert into SOURCE_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK7','P','T5',null,'ENABLED',null,'C2',null);
Insert into SOURCE_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK7','P','T5',null,'ENABLED',null,'C3',null);
  
CREATE TABLE TARGET_CONS
  (
    "CONSTRAINT_NAME"   VARCHAR2(30 BYTE),
    "CONSTRAINT_TYPE"   VARCHAR2(1 BYTE),
    "TABLE_NAME"        VARCHAR2(30 BYTE),
    "R_CONSTRAINT_NAME" VARCHAR2(30 BYTE),
    "STATUS"            VARCHAR2(8 BYTE),
    "OBJECT_TEXT"       VARCHAR2(2000 BYTE),
    "COLUMN_NAME"       VARCHAR2(30 BYTE),
    "ACTION"            VARCHAR2(30 BYTE)
  );
  
Insert into TARGET_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('FK2','R','TF2','PK2','ENABLED',null,'COL4',null);
Insert into TARGET_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('FK2','R','TF2','PK2','ENABLED',null,'COL5',null);
Insert into TARGET_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK1','P','T1',null,'ENABLED',null,'COL1',null);
Insert into TARGET_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK2','P','T2',null,'ENABLED',null,'COL4',null);
Insert into TARGET_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK2','P','T2',null,'ENABLED',null,'COL5',null);
Insert into TARGET_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK8','P','T5',null,'ENABLED',null,'C1',null);
Insert into TARGET_CONS (CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS,OBJECT_TEXT,COLUMN_NAME,ACTION) values ('PK8','P','T5',null,'ENABLED',null,'C2',null);

commit;


In this example, SOURCE_CONS has tabel name T2 has a primary key PK3 with columns COL4 and COL5. And TARGET_CONS had tabel name T2 has a primary key PK3 with columns COL4 and COL5.

As this columns in the constraint and table name matches and only constraint name is different, i need to return row for T2 from both tables to the program which renames this constraint in the target.

I have written the following query.

SELECT a.constraint_name as SRC_CNAME,
  a.table_name as SRC_TNAME,
  a.column_name as SRC_COL,
  b.constraint_name as TGT_CNAME,
  b.table_name as TGT_TNAME,
  b.column_name as TGT_COL  
FROM source_cons a,
  target_cons b
WHERE a.column_name   =b.column_name
AND a.constraint_name!=b.constraint_name
AND a.table_name      =b.table_name
AND a.constraint_type =b.constraint_type order by 1;


This query also returns data for table name 'T5'. T5 has three values in C1,C2 and C3 in source and two values in target C1 and C2. And so as C3 is missing in target this shouldn't be renamed. the query should not return data PK data for T5.

Help me to modify this query to return data where the table name matches, all column names matches (all columns should be present in both tbales) and the constraitn name is different.

Quote:

SRC_CNAME SRC_TNAME SRC_COL TGT_CNAME TGT_TNAME TGT_COL
------------------------------------------------------------
FK3 TF2 COL4 FK2 TF2 COL4
FK3 TF2 COL5 FK2 TF2 COL5
PK3 T2 COL4 PK2 T2 COL4
PK3 T2 COL5 PK2 T2 COL5
Re: Need help on SQL join. [message #584440 is a reply to message #584439] Tue, 14 May 2013 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use listagg:
SQL> WITH SOURCE_data AS
  2  (SELECT constraint_name, constraint_type, table_name,
  3   listagg(column_name, ',') WITHIN GROUP (ORDER BY column_name) cols
  4   FROM source_cons
  5   GROUP BY constraint_name, constraint_type, table_name),
  6  target_data AS
  7  (SELECT constraint_name, constraint_type, table_name,
  8   listagg(column_name, ',') WITHIN GROUP (ORDER BY column_name) cols
  9   FROM target_cons
 10   GROUP BY constraint_name, constraint_type, table_name)
 11  SELECT s.table_name, s.constraint_name, t.constraint_name
 12  FROM source_data s, target_data t
 13  WHERE s.cols = t.cols
 14  AND s.constraint_name != t.constraint_name
 15  AND s.table_name = t.table_name
 16  AND s.constraint_type = t.constraint_type;
 
TABLE_NAME                     CONSTRAINT_NAME                CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
TF2                            FK3                            FK2
T2                             PK3                            PK2
 
SQL> 
Re: Need help on SQL join. [message #584446 is a reply to message #584440] Tue, 14 May 2013 07:05 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
If you are not running 11g Release 2, but are running a version of the database where the WM_CONCAT function is present.
String Aggregation Techniques
Re: Need help on SQL join. [message #584458 is a reply to message #584446] Tue, 14 May 2013 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59113
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if you carefully read the post you'd see OP told he is in 11gR2.

Regards
Michel
Re: Need help on SQL join. [message #584534 is a reply to message #584458] Wed, 15 May 2013 02:36 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Thanks Cookiemonster, your logic helped me to rewrite my query to get the expected result set.
Re: Need help on SQL join. [message #584703 is a reply to message #584534] Fri, 17 May 2013 00:14 Go to previous messageGo to next message
Kevin Meade
Messages: 1938
Registered: December 1999
Location: Connecticut USA
Senior Member
Consider the use of COLLECT as well.

select s.constraint_name,s.constraint_type,s.table_name,t.constraint_name
from source_cons s
    ,target_cons t
where s.constraint_type = t.constraint_type
and s.table_name = t.table_name
group by s.constraint_name,s.constraint_type,s.table_name,t.constraint_name
having collect(s.column_name) = collect(t.column_name)
/


Good luck. Kevin

Good setup code, thanks.

[Updated on: Fri, 17 May 2013 00:16]

Report message to a moderator

Re: Need help on SQL join. [message #584739 is a reply to message #584703] Fri, 17 May 2013 06:49 Go to previous messageGo to next message
Kevin Meade
Messages: 1938
Registered: December 1999
Location: Connecticut USA
Senior Member
And of course one can always go OLD SCHOOL using RELATIONAL DIVISION.

--
-- assuming constraint_name is UNIQUE
--
select s.constraint_name,s.constraint_type,s.table_name,t.constraint_name
from (select distinct constraint_name,constraint_type,table_name from source_cons) s
    ,(select distinct constraint_name,constraint_type,table_name from target_cons) t
where s.constraint_type = t.constraint_type
and s.table_name = t.table_name
and not exists
   (
     select null
     from source_cons s2
     where s2.constraint_name = s.constraint_name
     and not exists
        (
          select null
          from target_cons t2
          where t2.constraint_name = t.constraint_name
--
          and t2.column_name = s2.column_name
        )
   )
/


There are more performant ways to write this using GROUP BY so what you see here is the pure form, but for such a small amount of data it works fine. It has the advantage of working on any SQL database regardless of feature set. Additionally it is not limited to the number of attributes that make up the set being compared though admittedly there are easy workarounds for the single attribute problem. Add... IT IS A PATTERN, so once you recognize the situation in a problem you are working on, you and just plug and play to get started on finding solutions.

Indeed I used this once at the beginning of my career some 28 years ago on an Oracle V3 database after finding it in one of DATE'S books. Took me a while to understand why it works (think Cartesian Product) because it makes no sense at first blush. But once you accept it, you have taken your first step into writing SQL by PATTERN. You may not use the actual relational division solution much, opting for other formulations of the SQL solution today, but making the mind shift form "writing SQL" to "understanding relational data processing patterns" is a very big deal. If you are interested, this book on SQL PATTERNS talks about it. This is a unique book for its subject matter. The value of the book is not so much in the specific examples, but in the idea of making the mental leap to a higher form of thinking when it comes to writing SQL.

Kevin

[Updated on: Fri, 17 May 2013 07:06]

Report message to a moderator

Re: Need help on SQL join. [message #584744 is a reply to message #584739] Fri, 17 May 2013 06:55 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've never come across collect before. Any idea where it's referenced in the documentation?
Re: Need help on SQL join. [message #584748 is a reply to message #584744] Fri, 17 May 2013 07:11 Go to previous message
Kevin Meade
Messages: 1938
Registered: December 1999
Location: Connecticut USA
Senior Member
Just Google "oracle collect", it will eventually lead you here. Not much help though so read on in articles from other authors.
Previous Topic: Ref cursor use
Next Topic: Truncate Master Table Issue
Goto Forum:
  


Current Time: Wed Sep 17 13:08:25 CDT 2014

Total time taken to generate the page: 0.06464 seconds