1

I need to write a very simple Firebird function

create or alter function some_function (A NUMERIC, B NUMERIC)
returns double precision
as
begin
 return       
   case
     when (B <= 2.5) then A - 2     
     when (B <= 5.0) then A - 1    
     else A
   end;
end;

However, if flags the first end; as an "Unexpected end of command error". My first thought was that maybe Firebird does not allow to return a searched case in a function, so I rewrote it as:

create or alter function some_function (A NUMERIC, B NUMERIC)
returns double precision
as
 declare result double precision;

begin
 result = case
   when (B <= 2.5) then A - 2     
   when (B <= 5.0) then A - 1    
   else A
 end;

 return result;
end;

But again, it flags the same error, now at the first end;. Firebird documentation about searched case (https://firebirdsql.org/refdocs/langrefupd15-case.html) even shows almost the same scenario. What is wrong? Of course, I solved it using traditional if then/else but I want to know if I misspelled something or maybe Firebird (WI-V3.0.7.33374 Firebird 3.0/64/W2012) has some bug.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
alvaroc
  • 433
  • 5
  • 14
  • 1
    How are you executing this (what tool)? It looks like you're using something that uses `;` to split queries before sending them to the server (eg Firebird's ISQL does that). If you're using a query tool like ISQL or FlameRobin, you may have to use `SET TERM` to switch the statement terminator (eg use `set term #;` at the start and `end#` instead of the **last** `end;`, followed by `set term ;#` to switch it back). – Mark Rotteveel Dec 18 '20 at 17:52
  • Mark, post your comment as a solution to vote it up. I used Eclipse and IBExpert and both flagged the error. Since neither of my scripts had a set term command and ran fine under both, I assumed I won´t have any problems with PSQL functions. I´ve never used them before until now. I wrote other functions without set term that ran fine with Eclipse (but didn´t with and old version of IBExpert) The problem started when using a searched case and since all scripts ran fine under Eclipse without set term, it never pointed my attention to such command. BTW Flamerobin always requires set term – alvaroc Dec 18 '20 at 19:00

0 Answers0