CREATE OR REPLACE FUNCTION mig_td_ext.mig_fn_castasint
/*  This function is used to support "CAST AS INTEGER" of Teradata.
    It should be created in the "mig_td_ext" schema.
*/
     ( i_param		    TEXT )
RETURN INTEGER
-- below line added as Gauss optimizer expects it for performance
IMMUTABLE 
AS
  v_castasint    INTEGER;
BEGIN

   v_castasint := CASE WHEN i_param IS NULL 
							THEN NULL	-- if NULL value is provided as input
					   WHEN TRIM(i_param) IS NULL 
							THEN 0		-- if empty string with one or more spaces is provided 
					   ELSE TRUNC(CAST(i_param AS NUMBER))	-- if any numeric value is provided
				  END;

   RETURN v_castasint;
END;
/

