Databases
StackOverflow2013
Postsdbo
POSQL Statement for Reconciliation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Statement for Reconciliation
    primarykey
    data
    text
    <p><strong>Given the schema below:</strong></p> <pre><code>create table TBL1 (ID varchar2(100) primary key not null, MATCH_CRITERIA varchar2(100)); create table TBL2 (ID varchar2(100) primary key not null, MATCH_CRITERIA varchar2(100)); create table TBL_RESULT (ID varchar2(100) primary key not null, TBL1_ID varchar2(100), TBL2_ID varchar2(100)); create unique index UK_TBL_RESULT_TBL1_ID on TBL_RESULT(TBL1_ID); create unique index UK_TBL_RESULT_TBL2_ID on TBL_RESULT(TBL2_ID); create sequence SEQ_TBL_RESULT; insert into TBL1 VALUES('1', '1'); insert into TBL1 VALUES('2', '1'); insert into TBL1 VALUES('3', '1'); insert into TBL2 VALUES('4', '1'); insert into TBL2 VALUES('5', '1'); insert into TBL2 VALUES('6', '1'); </code></pre> <p>I need an SQL statement that will produce the result by reconciling equal MATCH_CRITERIA from TBL1 and TBL2.</p> <p>The following would work given that the <em>UNIQUE constraint is not present</em>. However, <em>we need the unique constraints</em> in our application.</p> <pre><code>insert into TBL_RESULT (ID, TBL1_ID, TBL2_ID) select SEQ_TBL_RESULT.nextval, TBL1.ID, TBL2.ID from TBl1, TBL2 where TBL1.MATCH_CRITERIA = TBL2.MATCH_CRITERIA; </code></pre> <p><strong>Example output of TBL_RESULT</strong></p> <pre><code>| ID | TBL1_ID | TBL2_ID | | '1' | '1' | '5' | | '2' | '2' | '4' | | '3' | '3' | '6' | </code></pre> <p>Note: it doesn't matter if '1' is matched with '4' or '6'. As long as the MATCH_CRITERIA from both tables are equal and the resulting table has unique TBL1_ID and TBL2_ID.</p> <p>Note that we are inserting a million records from TBL1 and another million records from TBL2. Thus, sequential insert using PL/SQL is not acceptable unless it can run really fast (less than 15 minutes).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJohn
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Statement for Reconciliation
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Statement for Reconciliation
      1. USa_horse_with_no_name
      1. VTFavorite
    3. VO
      singulars
      1. POSQL Statement for Reconciliation
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload