I'm new to Firebird and need your help. I know that Firebird's task isn't to do a nice print.
I have a stored procedure with following reduced output:
Drink | Name |
---|---|
Cola | Marcus |
Cola | Siegfried |
Fanta | Jochen |
Beer | Anna |
Beer | Manuel |
I want to add a new row and column before the first value of each group of Drink with the name of the Drink in it.
So the output should be:
Drink | Drinks for print | Name |
---|---|---|
Cola | Cola | |
Cola | Marcus | |
Cola | Siegfried | |
Fanta | Fanta | |
Fanta | Jochen | |
Beer | Beer | |
Beer | Anna | |
Beer | Manuel |
I am using Firebird 2.5.8
Assuming you don't want to modify the existing stored procedure, but instead want to 'decorate' the output of the stored procedure, you would need to do something like this:
execute block
returns (
drink varchar(50),
"Drink for print" varchar(50),
name varchar(50))
as
declare drink_intermediate varchar(50);
declare name_intermediate varchar(50);
begin
for select drink, name
from drinks
order by drink
into drink_intermediate, name_intermediate
do
begin
if (drink is distinct from drink_intermediate) then
begin
drink = drink_intermediate;
"Drink for print" = drink_intermediate;
name = null;
suspend;
"Drink for print" = null;
end
name = name_intermediate;
suspend;
end
end
That is, each time the drink changes, you need to add an extra row.
You can also do something like this in DSQL, but this probably more expensive when you have a lot of rows due to the distinct
:
select distinct
drink,
case a.type when 1 then drink end as "Drink for print",
case a.type when 2 then name end as name
from drinks
cross join (
select 1 as type from RDB$DATABASE
union all
select 2 as type from RDB$DATABASE
) a
order by drink, type
That is, for each row of the stored procedure, we add an extra row with the "Drinks for print"
column populated and the name
not populated, and then we take a distinct (to remove the repeated rows), and sort for a consistent output.