How translate function from MySQL to Firebird?

Question

how how can i transfer this function to firebird

create function `candidat`(in_num   decimal(10,2),
                           in_group integer unsigned)   
       returns integer unsigned 
       deterministic   
       language sql 
begin   
   return case in_group when 1 then floor(in_num / 3.0 + 0.99)
                        when 2 then floor(in_num / 3.0 + 0.50)
                               else floor(in_num / 3.0) end; 
end

Answer 1

You can create Firebird 3 PSQL function, which will be almost identical to the MySQL function. The mayor differences are just the creation syntax:

create function candidat(in_num   decimal(10,2),
                         in_group integer)   
       returns integer
as
begin   
   return case in_group when 1 then floor(in_num / 3.0 + 0.99)
                        when 2 then floor(in_num / 3.0 + 0.50)
                               else floor(in_num / 3.0) end; 
end

As Firebird doesn't have unsigned integers, you need to use a normal (signed) integer instead. Given the input that should be sufficient, otherwise consider switching to bigint.

Answer 2

There are 3 ways do extract the data that you whant:

1. Add a CASE in your SQL:

   select
      case when :in_group = 1 then floor(:in_num / 3.0 + 0.99)
           when :in_group = 2 then floor(:in_num / 3.0 + 0.50)
           else floor(:in_num / 3.0) end
   from
      RDB$DATABASE
   

   OBS: The RDB$DATABASE it just a table example de result, and ":in_num" and ":in_group" its a variable/param, that you can change with a column of one table or just inject the value you need

2. Create a PROCEDURE like:

   SET TERM ^ ;
   
   CREATE OR ALTER procedure candidat (in_num decimal(10,2), in_group integer)
      returns ( calculed_value integer )
   as
   begin
     if (:in_group = 1) then
     begin
       calculed_value = floor(:in_num / 3.0 + 0.99);
     end
     else if (:in_group = 2) then
     begin
       calculed_value = floor(:in_num / 3.0 + 0.50);
     end
     else
     begin
       calculed_value = floor(:in_num / 3.0);
     end
   
     suspend;
   end
   ^
   
   SET TERM ; ^
   

   you can use it like:

   select 
      CALCULED_VALUE 
   from 
      candidat( 100, 2)
   

3. You can create a Library (UDF) using a language like C++ that generate a .dll ou .so (for linux) with the function candidat and than add it to the database.

   For this you can look at the documentation at https://www.firebirdsql.org/en/writing-udfs-for-interbase/

   Then you can use a UDF like:

   select
     candidat(1,3)
   from
     <TABLE>