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_at_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
 (
_at_old_numer INTEGER,
_at_old_denom INTEGER,
_at_old_origin_numer INTEGER,
_at_old_origin_denom INTEGER,
_at_new_origin_numer INTEGER,
_at_new_origin_denom INTEGER

 )
RETURNS INTEGER
AS
 BEGIN

  DECLARE _at_ret_num  INTEGER
  DECLARE _at_ret_den  INTEGER
  DECLARE _at_zoom_factor INTEGER
  DECLARE _at_is_magnified BIT

  IF _at_old_numer = @old_origin_numer AND @old_denom = @old_origin_denom    RETURN _at_new_origin_numer

  IF _at_old_origin_denom < @new_origin_denom    BEGIN
    SET _at_is_magnified = 0

    SET _at_zoom_factor = @new_origin_denom / @old_origin_denom     SET _at_ret_num = @old_numer - @old_origin_numer * @old_denom / _at_old_origin_denom

    SET _at_ret_den = @old_denom * @zoom_factor

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

    SET _at_zoom_factor = @old_origin_denom / @new_origin_denom     SET _at_ret_num = ( @old_numer - @old_origin_numer * @old_denom / _at_old_origin_denom ) * @zoom_factor

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

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

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

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

  RETURN _at_ret_num
 END Received on Mon Sep 15 2003 - 15:28:37 CEST