CODEWITHSUNDEEP
sqlfirebirdfirebird-psql
Jonathan Livingston Seagull
Jonathan Livingston Seagull

Reputation: 854

Is it possible to UNION two EXECUTE BLOCK statements?

Is there a way to perform a union of 2 sets where both were output by 2 different execute block commands?

The sets structure are the same but each one contains different parameters therefore I cannot easily merge both in one execute block command.

For example

EXECUTE BLOCK RETURNS -- set 1

AS DECLARE VARIABLE....my sql 1

BEGIN
FOR
..... my sql 1
DO
BEGIN
FOR
..... my sql 1 
DO BEGIN
SUSPEND;

END
END
END      UNION

EXECUTE BLOCK RETURNS -- set 2

AS DECLARE VARIABLE....my sql 2

BEGIN
FOR
..... my sql 2
DO
BEGIN
FOR
..... my sql 2 
DO BEGIN
SUSPEND;

END
END
END

Upvotes: 1

Views: 486

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

EXCUTE BLOCK cannot be used in a UNION. Only SELECT can participate in a UNION. If you want to union the results of two blocks of PSQL, you will need to create them as selectable stored procedures, instead of ephemeral EXECUTE BLOCK statements. With two selectable stored procedures, you can then apply UNION:

select * from procedure1
union
select * from procedure2

Alternatively, if you really need to use EXECUTE BLOCK, then you need to create a single EXECUTE BLOCK that creates the full output. I don't see what prevents you from creating a single execute block though: if the parameters differ, then you just need to add separate parameters for either, or - if it is about output parameters - rename things to make them usable by both.

Upvotes: 1

Related Questions