Hi Philip,
 
I think the sql you have suggested looks to be outstanding.
 
As i have said earlier, i am working on "UNION" sql, now "UNION ALL" has come into the picture. So it will require great understanding and patience to work out.
 
But i would like to be thankful for you as you have come up with master sql.
 
If it works out for me as per the requirement it will be "Master Trouble Shooter" for me.
 
 
I think its best opportunity to workout.
 
 
Thank You once again.
 
 
Have A Nice Day.
 
With Warm Regards
 
 
 
Vishal
 
 

--- On Wed, 21/4/10, unordained <unordained_00@...> wrote:


From: unordained <unordained_00@...>
Subject: Re: [firebird-support] How to add addition record in sql output
To: firebird-support@yahoogroups.com
Date: Wednesday, 21 April, 2010, 8:46 PM


 



---------- Original Message -----------
From: Vishal Tiwari <vishualsoft@ yahoo.co. in>
> 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-procedur e 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-recursiv e 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









[Non-text portions of this message have been removed]