How to properly display two unrelated select queries with no mutual fields in on procedure?
Table1
Number
1
2
3
4
5
Table2
Letter
a
When i try to call them using this procedure,
CREATE PROCEDURE SAMPLE
RETURNS(
Number SMALLINT,
Letter Varchar)
AS
BEGIN
FOR
SELECT
A.Number,
B.Letter
FROM Table1 A, Table2 B
INTO
:Number,
:Letter
DO
BEGIN
SUSPEND;
END
END;
i get this result
Number Letter
1 a
2 a
3 a
4 a
5 a
here is my desired result
Number Letter
1 a
2
3
4
5
Assuming you want a result set of unrelated sets 'zipped' up, you should assign each row from either table with a row number (using Firebird 3's
row_number()
) and then join on that (using an outer join, so it doesn't matter which has more rows).This would result in a query that looks like:
Note, depending on your needs, you may want to specify an explicit order for
row_number()
, for examplerow_number() over(order by number)
androw_number() over(order by letter)
.Alternatively, you could use the
CURSOR
support in Firebird's stored procedures and manual control the iteration, this gets quite messy and hard to follow though.You could do something like I show below (I'm using Firebird 3, but replacing those booleans with a smallint and using 1 and 0 should work with Firebird 2.0 - 2.5). For readability I used table names
numbers
andletters
instead oftable1
andtable2