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