What is the best strategy for merging an unknown number of resultsets into a single, sortable paginated resultset?
Background, I have a fairly complex query that users can build N times and then submit all of those queries at once. The N queries all return the same formatted result set but have wildly different WHERE clauses.
I could use an SPROC to generate dynamic sql statements based on their input or execute each query seperately and process it in the code, but there's got to be a better way to do it. Is there an optimal solution to this problem?
As long as the queries all return the same formatted result, you can just union them together and use order by to sort the result
select col1, col2, col3 from tablea where ...
union
select col1, col2, col3 from tablea where ...
order by col1, col2
If you care about knowing which query returned the result, you would need to add an extra column that indicated the query.
Depending on the size of the N number of queries that are generated, there could also be issues with the size of the total union query. I ran into issues with JDBC limiting the size of the query when trying to combine many queries this way and wound up doing individual database calls and merging the results in java code.