Help Center/ Database and Application Migration UGO/ User Guide/ Syntax Conversion/ Risky Custom Functions/ Replacing Custom Oracle Functions with GaussDB Functions/ remainder
Updated on 2025-06-07 GMT+08:00
View PDF
Share

remainder

Database Type and Version

  • Source database type and version: all Oracle versions
  • Target database type and version: GaussDB of versions earlier than V2.0-3.1

Function replacement syntax:

CREATE OR REPLACE FUNCTION dsc_ora_ext.dsc_fn_remainder
/* This function is used to replicate the behaviour of Oracle REMAINDER */
     ( i_numerator      IN   NUMBER
	 , i_denominator    IN   NUMBER )
RETURN NUMBER 
IMMUTABLE
AS 
    v_remainder      NUMBER;
BEGIN

    IF i_numerator IS NULL OR i_denominator IS NULL
    -- If anyone of the inputs is NULL, return NULL
    THEN 
        RETURN NULL;
    END IF;

    v_remainder := MOD(i_numerator, i_denominator);

    IF ABS(v_remainder/i_denominator) > 0.5 
    -- If the remainder (mod) is more than half of demonitor, do the below
	-- i.e., use ROUND instead of FLOOR used in MOD
    THEN
       v_remainder := i_numerator - (ROUND(i_numerator*1.0 / i_denominator) * i_denominator);
    END IF;

    RETURN v_remainder;
END;