Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Firebird SQL cursor repeat last row

to begin, sorry for my English. I'm starting with cursors in FirebirdSQL 2.5, writing some stored procedures for a sales register system. In this case I need to calculate the credit card's taxes that are cashed by the banks in my country from the sales that the system record. Here is the SP code:

create or alter procedure CURSOR_POC
returns (
  ID int,
  PRICE decimal(15,2),
  TAX decimal(15,2))
as
declare variable GO_ON char(1);
declare MY_CURSOR cursor for (
  select CARD.CODE, sum(SALE.PRICE)
  from CARD join SALE on CARD.CODE = SALE.CARD_CODE
  where SALE.SALE_DATE = '14.10.2015'
  group by CARD_CODE);
begin
  open MY_CURSOR;
  GO_ON = 'Y';
  while(GO_ON='Y') do
  begin
    fetch MY_CURSOR into :ID, :PRICE;
    if (row_count = 1) then
    begin
      TAX = PRICE * (select CARD.TAXES from CARD where CARD.CODE = :ID);
      suspend;
    end else GO_ON = 'N';
    end
close MY_CURSOR;
suspend;
end

When I run the SP, its execute just fine but always show the last row twice. For instance, if I run the query in a separate file Firebird give me 10 rows, but when I run the SP I got 11 rows. Any help will be grateful. In advance, thank you very much.

P.D.: I "translate" the tables and attributes names in order to make it more understable.
P.D.2: the date filter ("dd/mm/yyyy" format) in the definition of the cursor was only define to test de stored procedure.

like image 366
Sebastián Reverso Avatar asked Aug 01 '26 03:08

Sebastián Reverso


1 Answers

I will answer myself (a professor help me in a private forum from my University).
The solution was as simple as erase the last "suspend" from the stored procedure. So that, the code should be

(...previous code...)

if (row_count = 1) then
begin
  TAX = PRICE * (select CARD.TAXES from CARD where CARD.CODE = :ID);
  suspend;
end else GO_ON = 'N';
end
close MY_CURSOR;
end
like image 92
Sebastián Reverso Avatar answered Aug 04 '26 06:08

Sebastián Reverso



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!