1 Reply Latest reply on Jul 26, 2013 8:43 AM by Mike-Matthews-Oracle

    Most effective solution to join reader streams on primary key


      How can two reader input streams be joined on a primary key most effectively? For example, I have two readers, one with person data, one with address data; both have a primary key that can be used for a join. OEDQ provides a number of Match nodes, in addition to the Merge Streams node, but none of these options provides a simple way to create a join. Would combining a Merge Streams node with a Group and Merge node be the best way? Thanks

        • 1. Re: Most effective solution to join reader streams on primary key

          No, that would not work.


          Group and Merge is like a select distinct on a single stream. Merge Data Streams is a simple way of collating all records from multiple inputs.


          The way to do what you want is not to use two readers but to define a lookup against the data you want to join against and use a Lookup and Return processor to bring the data together. If you are doing a 1:M lookup you can then use a single Split Records from Array processor to create a record in your stream for each looked up record (a 'join').


          In some cases - if you just want to join the data together before processing it rather than checking consistency etc. - it makes sense to do the join in the snapshot by specifying the snapshot as SQL or using a View... obviously this approach assumes direct connectivity to the DB though.