I have a question around the use of temporary tables. Here’s scenario:
LOGIC BLOCK 1:
Creates, clears and populates a temporary table (Shared and Allow Multiples)
Once temporary table is populated, a fetch of temp table with sort order on 1 field is done. In the data found, a LOGIC BLOCK 2 is called.
LOGIC BLOCK 2:
Creates temporary table (Shared and Allow Multiples)
A fetch of temp table with a different sort order field is done and in the data found, logic is run.
Would you expect the sort in LOGIC BLOCK 2 to affect the sort in LOGIC BLOCK 1? I know this is an odd scenario, but there was reason for the logic in this case. We had issues with this scenario and only once we removed the sort in LOGIC BLOCK 2 did it resolve.
I would. Not because that makes any sense, but because that’s how it was implemented and now it’s too late to make it do anything different without breaking backward compatibility.
The implemented behavior of sorting temp tables (shared or not) is to actually reorder/mutate the in-memory list. The real list. Not a deep copy. So yes, if you nest loops of the same temp table you are absolutely going to get surprising results.
I would love for you to explore the List construct (scalars or records). It does not yet support sort, but in every other way it is a logical replacement for/improvement over temp tables. It can be passed through LB boundaries as input and/or output and at the boundary is a deep copy so it doesn’t matter what the called LB does to it. Since there is no sort, I would like to hear about the performance if your lists are long and have you push for sort if you need it. These types of scenarios of nested loops is why we chose not to implement sort without solid requirements.
@kendra.b Noting this, thank you! And, will keep the List in consideration for future work where it makes sense to do so. Will share this with the team as well and let you know if the sort is needed. Thank you.
