1

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
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Jonathan Livingston Seagull
  • 844
  • 10
  • 22
  • Your code suggests that what you have a N + 1 query problem. You might want to check carefully if that is really need (most N + 1 queries can be simplified to a single query by using joins). – Mark Rotteveel Feb 17 '21 at 18:03
  • Hi Mark, the N +1 query problem is something new for me, although I might have crossed it occasionally without acknowledging it. For what I have read, my query is not what this is about, I guess... – Jonathan Livingston Seagull Feb 17 '21 at 18:56

1 Answers1

1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • All right Mark. I thought it would be possible to UNION the EXECUTE BLOCK, but for what you say I might have to review my SQL code in order create a single execute block. Let´s work on it then. Thank you. – Jonathan Livingston Seagull Feb 17 '21 at 18:56
  • @JonathanLivingstonSeagull It might be an interesting feature. I'll think about adding an improvement ticket to the Firebird issue tracker. – Mark Rotteveel Feb 17 '21 at 19:41
  • Great @MarkRotteveel ! – Jonathan Livingston Seagull Feb 17 '21 at 19:52
  • actually, current EB statement is somewhat erratic, it goes `out of tune`. Would there just be `SELECT * from EB` like there is `SELECT * from SP` and there would not be a problem to start with. Except for being chatty, but that is what SQL is about :-) // Also, @MarkRotteveel , what about using EBs in CTEs ? If it is not possible - then can you add it to the ticket. If it is, then you probably can do UNION of CTEs :-D – Arioch 'The Feb 19 '21 at 10:51
  • @Arioch'The I'm going to suggest to allow EXECUTE BLOCK where derived tables are allowed, no need for `select * from `, maybe just `` or `()` should be sufficient; applying as CTE should be possible to. – Mark Rotteveel Feb 19 '21 at 12:01