Databases
StackOverflow2013
Postsdbo
POSQL Statement for Reconciliation with different operators
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Statement for Reconciliation with different operators
    primarykey
    data
    text
    <p>This is very related to question: <a href="https://stackoverflow.com/questions/7817028/sql-statement-for-reconciliation">SQL Statement for Reconciliation</a> but with a more twist.</p> <p><strong>Given the schema below:</strong></p> <pre><code>create table TBL1 (ID varchar2(100) primary key not null, MATCH_CRITERIA timestamp); create table TBL2 (ID varchar2(100) primary key not null, MATCH_CRITERIA timestamp); 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); insert into TBL1 VALUES('1', to_date('01/26/2012 20:00:00', 'mm/dd/yyyy hh24:mi:ss')); insert into TBL1 VALUES('2', to_date('01/26/2012 20:05:00', 'mm/dd/yyyy hh24:mi:ss')); insert into TBL2 VALUES('3', to_date('01/26/2012 19:59:00', 'mm/dd/yyyy hh24:mi:ss')); insert into TBL2 VALUES('4', to_date('01/26/2012 20:04:00', 'mm/dd/yyyy hh24:mi:ss')); </code></pre> <p><strong>Our current query:</strong></p> <pre><code>INSERT INTO TBL_RESULT (ID, TBL1_ID, TBL2_ID) SELECT rawtohex(sys_guid()),t1.id,t2.id FROM (SELECT t1.match_criteria,t1.id, row_number() OVER (PARTITION BY t1.match_criteria ORDER BY t1.id) rn FROM tbl1 t1) t1, (SELECT t2.match_criteria,t2.id, row_number() OVER (PARTITION BY t2.match_criteria ORDER BY t2.id) rn FROM tbl2 t2) t2 WHERE t1.match_criteria between t2.match_criteria - (10/1440) AND t2.match_criteria + (10/1440) AND t1.rn=t2.rn </code></pre> <p><strong>It's output:</strong></p> <pre><code>| ID | TBL1_ID | TBL2_ID | | '1' | '1' | '3' | | '2' | '1' | '4' | | '3' | '2' | '3' | | '4' | '2' | '4' | </code></pre> <p>As you can see, results were not compliant to the unique constraint (duplicate TBL1_ID / duplicate TBL2_ID). This is because: </p> <ol> <li>The RN for each record is always 1 (thus always equal)</li> <li>The date between the two records is 10 minutes.</li> </ol> <p><strong>We are expecting an output that looks like the table below:</strong></p> <pre><code>| ID | TBL1_ID | TBL2_ID | | '1' | '1' | '4' | | '2' | '2' | '3' | </code></pre> <p>Note 1: it doesn't matter if '1' is matched with '3', but then '2' should be matched to '4' to comply with the constraints and as long as the T1.MATCH_CRITERIA is within 10 minutes of T2.MATCH_CRITERIA.</p> <p>Note 2: 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> <p>Note 3: unmatched data should be eliminated. Unbalanced data is also expected.</p> <p>Note 4: we are not limited to execute 1 query only. A series of finite queries will do.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJohn
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat happens if there are rows in t1 that cannot be matched with rows in t2 (or vice versa)? Do you eliminate that data? Or would you expect to end up with output where either `TBL1_ID` or `TBL2_ID` would be NULL?
      singulars
      1. POSQL Statement for Reconciliation with different operators
      1. USJustin Cave
    2. COBTW your test data uses `MM` in the format mask twice. You meant `MI` for minute the second time around. It's a common mistake.
      singulars
      1. POSQL Statement for Reconciliation with different operators
      1. USAPC
    3. CO@JustinCave, eliminate the data.
      singulars
      1. POSQL Statement for Reconciliation with different operators
      1. USJohn
 

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