Topic
  • No replies
LukeNumrych
LukeNumrych
90 Posts

Pinned topic No UNION for a query on a table function?

‏2014-01-21T17:14:06Z |

I wrote this function that returns a table result - what it does is not important.  If I run it separately with two distinct sets of parameter like so:

select * from table (func1(a1,b1,c1...));

select * from table (func1(a2,b2,c2...));

I get two separate sets of results just fine; however, if I try to do this:

select * from table (func1(a1,b1,c1...))

union

select * from table (func1(a2,b2,c2...));

I get "The function "func1" modifies SQL data and is invoked  in an illegal context. Reason code =  "2".. SQLCODE=-20267, SQLSTATE=429BL, DRIVER=4.16.53"... I get it why - I looked up SQL20267N, but this seems to limit the functionality somewhat...

Thankfully it is easy to rewrite the query as:

WITH 
T1 AS (select * from table (func1(a1,b1,c1...))),
T2 AS (select * from table (func1(a2,b2,c2...)))
SELECT * FROM T1
UNION
SELECT * FROM T2;