· Mar 11, 2021

Possible to merge two %ResultSet ?

Hi! I have a complex SQL select query which I execute via %ResultSet. It also includes a UNION. The select statement within the union should only be done when an external parameter is set, and I'm not sure of way of doing that within SQL, other than something like


WHERE (some conditions)



WHERE :doUnion = 1 AND (some conditions)

...this works, but I don't think it's particularly optimal as I believe it will still execute the second SELECT regardless of :doUnion. It's also sort of hard to read...

It might be there's a better way of doing this in SQL, but I was also curious if I could split the two SELECTs into seperate queries, and when doUnion is set, execute the second query and then merge the resultsets somehow? Something like:

set rs=##class(%ResultSet).%New("Query1")
set sc=rs.Execute()

if doQuery2 {
  set sc=rs2.Execute()
  merge rs=rs2

quit rs

Ideally merging in the same way that UNION does - ie, ignoring duplicates.


Product version: IRIS 2019.1
Discussion (12)1
Log in or sign up to continue

Yes I can put a UNION within a single Class_Query - that's my current approach (see first post), but the issue I have is disabling the UNION half, either within the SQL statement (inelegant and it appears inefficient, at least the way I tried in my first post), or somehow running two Class_Queries into one ResultSet (with the second being enabled at runtime based on a parameter).

The queries are too complex to maintain as strings and concatenate them together that way. They need to remain Class_Queries.

Oh I see! But doesn't that leave the same issue I have with disabling the UNION'd second half at run time? The only way I can think of is passing it in as a parameter and have the second WHERE clause including

WHERE :doUnion = 1

...but that will still do the second query, and discard it after the fact, I believe, which is inefficient. It might be possible to do with via a CASE statement or IF ELSE somehow? Not sure...