coverting a SQL Server function into a TD SQL UDF

Database
Enthusiast

coverting a SQL Server function into a TD SQL UDF

Hi guru's,

I am faced with a problem of converting a SQL Server function into a TD SQL UDF. As we are all aware that Teradata is not flexible with the usage of "set/declare/loop" etc etc and I am wondering if there is a way to still code this with the limited functionality. The function I am trying to convert is as below and I am on TD 13.10 db version. Although we can easily do this in a Store procedure, it does not solve my purpose. I thought of recursive SQL, but not sure if I can do this. Your help is greatly appreciated.

CREATE FUNCTION [dbo].[Symmetrical_Probability_Approximation]

(

      @N int,

      @radius real,

      @precision real,

      @distance real

)

RETURNS real

AS

BEGIN

      declare @sigma real;

      declare @AP real;

      declare @result real;

      declare @OPj real;

      declare @OPi real;

      declare @Aj real;

      declare @Ai real;

      declare @pj real;

      declare @pi real;

      declare @i int;

      declare @Ri real;

      set @sigma=@precision/2.146;

      set @AP=3.14159265*@radius*@radius;

      set @OPj=0.0;

      set @Aj=0.0;

      set @pj=0.0;

      set @result=0.0;

      set @i=0;

      While (@i<@N) Begin

            set @i=@i+1;

            set @Ri=(1.5*@i*@precision)/@N;

            set @Ai=3.14159265*@Ri*@Ri;

            set @pi=1-exp(-(@Ri*@Ri)/(2*@sigma*@sigma));

            if (@Ri+@radius<=@distance) set @OPi=0;

            else if (@distance+@radius<=@Ri) begin

                  set @OPi=@AP;

                  set @i=@N;

            end

            else if (@distance+@Ri<=@radius) set @OPi=@Ai;

            else set @OPi=@radius*@radius*acos((@distance*@distance+@radius*@radius-@Ri*@Ri)/(2*@distance*@radius))+

            @Ri*@Ri*acos((@distance*@distance+@Ri*@Ri-@radius*@radius)/(2*@distance*@Ri))-0.5*sqrt((@Ri+@radius-@distance)*(-@Ri+@radius+@distance)*(@Ri-@radius+@distance)*...));

            set @result=@result+(@pi - @pj)*(@OPi-@OPj)/(@Ai-@Aj);

            set @OPj=@OPi;

            set @Aj=@Ai;

            set @pj=@pi;

      end

      RETURN @result;

END

Thank you.

1 REPLY
Enthusiast

Re: coverting a SQL Server function into a TD SQL UDF

Hi experts,

Any ideas or suggestions are greatly appreciated.

Thank you.