LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

I have two streams, one stream is individual discreet values, the second stream consists of start range and end range values. I would like to join these two streams.

After further thought, the ability to join using a boolean expression. It would be a requirement that the streams be sorted. And the simple case of the boolean expression would be an equal expression. But it should be generalized to allow less than and greater than expressions and any other boolean expression involving values from both streams.

We can make some rules about sorting to require that once a row in one input no longer matches in the join it no longer needs to be checked.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mmarinelli

Agreed, we should support more control over the conditions for a successful match, in addition to the built-in equivalence. When I have to do something like this, I just match on 1=1 and use the where clause in the emit statement to apply the desired matching logic (e.g. emit * where 1:FOO < 2:BAR_HIGH and 1:FOO >= 2:BAR_LOW), but this can be really slow if the join is applied to a large set of data, since the join is still calculating the cartesian product of the inputs, even if it's only outputing a subset thereof. To my knowledge, there is no other way to do this currently.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

Yes. I'm glad I'm not the only one. I've seen joins on 1=1 to merge the one record on one input to all the records on another input many times. I've done the same thing with a lookup node before seeing this.

The thing that made me post this was having to join two streams of over 1 million records each. I thought seriously about doing an outer join like you mentioned, but his would end up with BRAIN having to evaluate trillions of combinations. Or in other words it would have to read one input file one time for every record in the other input file.

Here is the answer I put in another stream. I had one input of ESN values and another input with ESN ranges defined by ESNMIN and ESNMAX. I don't know if this link will work, but here it is: http://www.professionalbrain.com/ind...61&catid=7 It is titled "Join to a range"

			
				I was able to do this operation by using several nodes.


The second input which is the ranges went to two sorts, one sorted on ESNMIN and the other sorted on ESNMAX. The first input was, of course, sorted on ESN. Then I joined the sorted first input to the ESNMIN sorted input. Then I joined the sorted first iput to the ESNMAX sorted input. For this second join I had to remove duplication with the first join by removing any matches where the record had ESNMIN == ESNMAX since they would be showing up in both. So this takes care of all records that match either the start or an end of a range but not the ones in the middle.


To take care of the middle I concatenated the first input with the second input keeping all columns. Then sorted the result on Nvl(ESN,ESNMIN). Since I wasn't worried about any matches where ESN==ESNMIN anymore I didn't care what order they ended up in. Next I went through the data with a Filter node taking advantage of the fact that you can actually keep data from one record to the next if done carefully. (see answers to my other posts) I could keep the ESNMIN and ESNMAX values and only output records where I had a non-null ESN value that was between, non-inclusively, the ESNMIN and ESNMAX values of the saved data.