Join using between clause

[Majid]

Hi All,

I am looking for directions on how to perform a join based on between clause. I would like to join these 2 sources based on Source1.Key between source2.Key Start and source2.Key End

Source 1 :

|Key|Name|
|0|ABC0|
|1|ABC1|
|2|ABC2|
|3|ABC3|
|4|ABC4|
|5|ABC5|
|6|ABC6|
|7|ABC7|
|8|ABC8|
|9|ABC9|
|10|ABC10|
|11|ABC11|
|12|ABC12|
|13|ABC13|
|14|ABC14|
|15|ABC15|
|16|ABC16|

Source 2 :

|Key Start|Key End|Grp_number|
|0|0|1|
|1|4|2|
|5|10|3|
|11|13|4|
|14|16|5|

Expected Output :

|Key|Name|Key Start|Key End|Grp_number|
|0|ABC0|0|0|1|
|1|ABC1|1|4|2|
|2|ABC2|1|4|2|
|3|ABC3|1|4|2|
|4|ABC4|1|4|2|
|5|ABC5|5|10|3|
|6|ABC6|5|10|3|
|7|ABC7|5|10|3|
|8|ABC8|5|10|3|
|9|ABC9|5|10|3|
|10|ABC10|5|10|3|
|11|ABC11|11|13|4|
|12|ABC12|11|13|4|
|13|ABC13|11|13|4|
|14|ABC14|14|16|5|
|15|ABC15|14|16|5|
|16|ABC16|14|16|5|

[Majid]

@cjhoward18 is there any other recommendation for achieving join based on between clause

[koryknick]

What is the source of this data? Is this coming from a file or a database or what?

Can you do the join in the source before it it sent to SnapLogic? Generally, it is preferred to do that type of work (i.e. joins, filters, sorts, and aggregates) in the source when possible.

[Majid]

Hi @koryknick I am not able to do join at DB level as my source files are Mainframe binary format VSAM files with COMP3 fields in AWS S3. I am using Copybook parser to change the files from EBCDIC into ASCII using the corresponding copybook before I can join them.

[koryknick · Accepted solution]

OK - got it. SnapLogic does not support the “between” operation in Join snaps. So to accomplish this in a streaming manner, I propose the attached. Basically, it generates the missing records on the bottom path before the join. This should keep memory overhead low since the only arrays kept in memory are the arrays generated to fill the gaps.
Join Between_2021_06_29.slp (10.1 KB)

Of course, this does produce many more records on that bottom path, but since you are using Inner join, the memory in the Join is still low as well.

[Majid]

Thank you so much @koryknick. This is working as expected and also performance issue is resolved using this approach. I had earlier tried to use sequence generator to generate multiple documents by passing start and end values as parameters but that approach was taking lot of time because it was triggering millions of child pipeline with sequence generator.