---------- Original Message -----------
From: Vishal Tiwari <vishualsoft@...>

> I have a small doubt(and requirement also), if i am executing an sql
> statement and it is giving 10 records as an output, is it possible
> to get some extra rows with this output with some initial values or
> with no values for specified or for all columns.   For example, if i
> am getting 10 records as an output, is it possible to get an extra
> records means total 11 records (i.e. one extra record with or without
> values).   As per my efforts it seems to be not possible, but still i
> would like to clarify this doubt.   But i would like to do this effort
> with sql statement.   Thanks in advance.     Vishal

------- End of Original Message -------

By "with sql statement", do you mean you want one, and only one, statement that
does this, with no help from stored procedures? SPs seem like an appropriate
way to solve the problem, but here's a non-stored-procedure solution first. I
do NOT recommend actually using this:



with recursive blank as (
select 0 as a, 0 as b, 0 as c from rdb$database
union all
select 0 as a, 0 as b, 0 as c from rdb$database inner join blank on 1=1
)
select first 11 a, b, c from
(
select a, b, c from actual_data
union all
select a, b, c from blank
) padded_data;



I have a feeling there's an upper limit on how much data you can pull from that
infinitely-recursive query, but I can't tell you what, exactly. I think there's
a max recursion depth, but at each recursion, the dataset doubles (?) in size,
so you're probably looking at billions of possible blank rows before Firebird
shuts you down? Can anyone correct me on this one?



An in-between solution would be:

create procedure blank_data (default_a integer, default_b integer, default_c
integer) returns (a integer, b integer, c integer)
as
begin
a = default_a;
b = default_b;
c = default_c;
while (1=1) do
suspend;
end

select first 11 a, b, c from
(
select a, b, c from actual_data
union all
select a, b, c from blank_data(0,0,0)
) padded_data;



As long as you don't do anything weird with ORDER BY, I think that'll work
correctly. Unions aren't theoretically guaranteed to return data in the order
you asked for it, so I'm still a little uneasy about it. If you really want to
make sure:



create procedure padded_data returns (a integer, b integer, c integer)
as
declare variable i integer;
begin
for select a, b, c from actual_data into :a, :b, :c do suspend;
i = row_count;
while (i < 11) do
begin
a = 0;
b = 0;
c = 0;
suspend;
i = i + 1;
end
end

select a, b, c from padded_data;

-Philip