Wanted to share the solution here. The query was right, no need to change the “Fetch-size” as well.
The only thing that was missing was ’ ’ for the IDs that we were passing in the query. Thus, the query would look like -
WHERE NAME IN (‘102345’, ‘1701’, ‘5604878’)
The Snowflake Lookup snap covers this functionality. That will allow lookup on any number of ids, it will internally batch as required. Also, Lookup snap will use bind parameters, avoiding possible issues with SQL Injection which can happen with a SQL statement string constructed in the Select snap.
Hey @Walkback,
Use the .map() method on $orderLine. The expression should look something like this:
$orderLine.map(x=>x.extend({"countryIso" : "GB","sessionId" : "ed7e8228-a82b-44e0-adea-caee6c61a987"}))
Here’s also a sample pipeline:
Extend objects in array_2021_10_28.slp (3.9 KB)
Regards,
Bojan
Bojan
Thank you for your feedback, but I have already tried that. I’m currently using a mapper snap (which maybe the wrong way to do it) used the data once, but there are after errors, because the 2 values are both null.
Is there a means of retaining the 2 values?
Jay
What should happen if the values are null? You can either filter those with a filter function:
$orderLine.filter(y=>y!=null)
or keep them as nulls with extending the expression with a ternary operator:
$orderLine.map(x=>x != null ? x.extend({"countryIso" : "GB","sessionId" : "ed7e8228-a82b-44e0-adea-caee6c61a987"}) : x)
It’s not clear how your countryIso and sessionId fields enter the pipeline (are they constants, pipeline parameters?), but I’ve attached an example that’s pretty simple and doesn’t involve javascript expressions. Uses a Splitter to break out the lines, then a mapper to add the countryIso and sessionId (orderId?), then a GroupBy to put it all back together. You can use a JSON formatter to spit it back out depending on what your are doing with it. Hope this gives you some ideas anyway.
walkbackCopyData_2021_10_28.slp (7.7 KB)
Thank you for your support and suggestions. I solved this problem, using a stored procedure.
