CODEWITHSUNDEEP
mysqlfirebirdfirebird-3.0
Sahat
Sahat

Reputation: 29

How translate function from MySQL to Firebird?

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

Upvotes: 0

Views: 246

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109015

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.

Upvotes: 0

dennnis
dennnis

Reputation: 44

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>

Upvotes: 1

Related Questions