Re: Tropashko nested sets and materialized path - great idea but how do I insert?

From: Robin Tucker <r.tucker_at_thermoteknix.com>
Date: Mon, 15 Sep 2003 14:28:37 +0100
Message-ID: <bk4eo5$3o6$1$8302bc10@news.demon.co.uk>

I wondered about the is_magnified variable too. Changed it from BOOLEAN to BIT before realising that it isn't needed. Anyway, this is the T-SQL version below - it works like a charm.

Thanks people.

CREATE FUNCTION dbo.func_Move_Numerator
 (
@old_numer INTEGER,
@old_denom INTEGER,
@old_origin_numer INTEGER,
@old_origin_denom INTEGER,
@new_origin_numer INTEGER,
@new_origin_denom INTEGER

 )
RETURNS INTEGER
AS
 BEGIN

  DECLARE @ret_num  INTEGER
  DECLARE @ret_den  INTEGER
  DECLARE @zoom_factor INTEGER
  DECLARE @is_magnified BIT

  IF @old_numer = @old_origin_numer AND @old_denom = @old_origin_denom    RETURN @new_origin_numer

  IF @old_origin_denom < @new_origin_denom    BEGIN
    SET @is_magnified = 0

    SET @zoom_factor = @new_origin_denom / @old_origin_denom     SET @ret_num = @old_numer - @old_origin_numer * @old_denom / @old_origin_denom

    SET @ret_den = @old_denom * @zoom_factor

    SET @ret_num = dbo.func_Normalize_Numerator ( @ret_num )     SET @ret_den = dbo.func_Normalize_Denominator ( @ret_num, @ret_den )    END
  ELSE
   BEGIN
    SET @is_magnified = 1

    SET @zoom_factor = @old_origin_denom / @new_origin_denom     SET @ret_num = ( @old_numer - @old_origin_numer * @old_denom / @old_origin_denom ) * @zoom_factor

    SET @ret_den = @old_denom
    SET @ret_num = dbo.func_Normalize_Numerator ( @ret_num )
    SET @ret_den = dbo.func_Normalize_Denominator ( @ret_num, @ret_den )

   END   IF @ret_den < @new_origin_denom
   BEGIN
    SET @ret_num = @new_origin_numer + @ret_num * @new_origin_denom / @ret_den

    SET @ret_den = @new_origin_denom
   END
  ELSE
   BEGIN
    SET @ret_num = @new_origin_numer * @ret_den / @new_origin_denom + @ret_num

    SET @ret_den = @ret_den
   END   SET @ret_num = dbo.func_Normalize_Numerator ( @ret_num )   SET @ret_den = dbo.func_Normalize_Denominator ( @ret_num, @ret_den )

  RETURN @ret_num
 END Received on Mon Sep 15 2003 - 08:28:37 CDT