Possible to merge two %ResultSet ?

Question

Question

Chris Bransden · Mar 11, 2021

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

SELECT FOO FROM BAR

WHERE (some conditions)

UNION

SELECT FOO2 FROM BAR2

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 {
rs2=##class(%ResultSet).%New("Query2")
set sc=rs2.Execute()
merge rs=rs2
}

quit rs

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

Thanks,
Chris

Product version: IRIS 2019.1

Discussion (12)1

Log in or sign up to continue

Robert Cemper · Mar 12, 2021

Using Class_Queries like a table the SQL Statement may look similar to this example:

select 'Q1' as Qry ,ID,name,SSN
  from sample.SP_sample_by_name() where name %startswith 'A' 
UNION
select 'Q2','--',SSN,name
  from sample.employee_byname() where name %startswith 'K'

with this result:

0 0

score 0 · Answer 1 · 2021-03-11T09:43:51-05:00

I'd do an if before you create the result set.

if doUnion{
    set myquery = "query 2"
}
else{
    set myquery = "query 1"
}

set rs = ##class(%ResultSet).%New(myquery)

score 0 · Answer 2 · 2021-03-11T09:54:32-05:00

sorry, i've probably not been clear, but for scenario 'doUnion' I want to run query 1 AND query 2 into the resultset. effectively what would happen if you used union statement.

i could create two queries, first with query 1 unioned with 2, and second with just with query 1, but that seems a bit ugly from a maintenance/code duplication perspective.

score 0 · Answer 3 · 2021-03-11T12:23:51-05:00

To expand on David's answer, you'd have to do something like

S query = "query 1"

S query2 = " UNION query 2"

I doUnion S query = query_query2

S rs = ##class(%ResultSet).%New(query)

.....

score 0 · Answer 4 · 2021-03-11T18:00:18-05:00

These are both in-class query declarations with parameters, like:

Query query1(foo, bar) As %Library.SQLQuery
{
SELECT ....
}

So I don't think you can append commands to them in this way?

score 0 · Answer 5 · 2021-03-12T08:28:31-05:00

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.

score 0 · Answer 6 · 2021-03-12T11:00:19-05:00

maybe a misunderstanding:
I call both Class Queries as Tables and join them with UNION
without modifying the existing class queries

score 0 · Answer 7 · 2021-03-12T11:53:18-05:00

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...

score 0 · Answer 8 · 2021-03-12T12:41:58-05:00

Robert Cemper · Mar 12, 2021

It is not meant to replace one of the queries:
do #1 or #2 or do both by UNION

0 0

score 0 · Answer 9 · 2021-03-14T11:38:43-04:00

Oh, I see! Yes this is perfect - I can create a simple wrapper query with the UNION between calls to Query1 and Query2, and call that when appropriate. All working here! Thanks so much

score 0 · Answer 10 · 2021-03-14T11:58:53-04:00

Robert Cemper · Mar 14, 2021

BINGO ! you got the idea behind

0 0

score 0 · Answer 11 · 2021-03-14T15:42:51-04:00

Robert Cemper · Mar 14, 2021

Thanks for the inspiration for this article
Using ClassQueries() as Tables

0 0