StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>At the moment your query produces a cross join, because your business rules fail to provide a mechanism to link one record in T1 with just one record in T2. Given that this is obviously a toy example it is difficult for us to suggest anything other than something very simplistic:</p> <pre><code>(SELECT t1.match_criteria,t1.id, row_number() OVER (ORDER BY t1.match_criteria,t1.id) rn .... (SELECT t2.match_criteria,t2.id, row_number() OVER (ORDER BY t2.match_criteria,t2.id) rn </code></pre> <p>This will simply match the first row in the T1 resultset with the first row in the T2 resultset, the second row in the T1 resultset with the second row in the T2 resultset, and so on. </p> <pre><code>SQL> INSERT INTO TBL_RESULT (ID, TBL1_ID, TBL2_ID) SELECT seq_tbl_result.nextval,t1.id,t2.id FROM (SELECT t1.match_criteria,t1.id, row_number() OVER (ORDER BY t1.match_criteria, t1.id) rn FROM tbl1 t1) t1, (SELECT t2.match_criteria,t2.id, row_number() OVER (ORDER BY t2.match_criteria, 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 SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 / 2 rows created. SQL> select * from tbl_result 2 / ID TBL1_I TBL2_I ------ ------ ------ 9 1 3 10 2 4 SQL> </code></pre> <p>That probably isn't what you want. In which case you need to explain your data and the rules for deciding what links with what. For instance is there some sort of pattern to either set of times which would allow you to derive an anchor point?</p> <hr> <p>As an aside, when I rule the world people who use VARCHAR2(100) columns to hold numeric IDs will be shot.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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