>>>>>Hi,
>>>>>
>>>>>I posted the following in the .NET forum but it's essentially a SQL query problem so I thought I'd try here as well:
>>>>>--------------->
>>>>>Assume a table where each row contains the specification for a rectangle but which also may 'inherit' from a parent row. E.g.:
>>>>>

>>>>>ID     ParentID   Top    Left   Width    Height    Color
>>>>>1      0          10     10     100      100       White
>>>>>2      1          null   null   200      null      null
>>>>>3      2          null   null   null     null      Red
>>>>>4      1          20     20     null     null      Green
>>>>>* where non-null values override the parent value. This means ID 3 would be:
>>>>>                  10     10     200      100       Red
>>>>>* ID 4 would be:
>>>>>                  20     20     100      100       Green
>>>>>* etc
>>>>>

>>>>>I had this coded in VFP using recursion to 'walk' back up the tree to the root record and then build the final spec by unwinding back down. Any suggestion on how to implement this using ADO/SQLServer (with as much as possible in SQL since this data may also be accessed by other front ends)?
>>>>>
>>>>>Bear in mind that the example above is a (gross) over-simplification of the actual table structure and that the nesting levels will often be deeper...
>>>>>
>>>>>TIA,
>>>>>Viv
>>>>
>>>>

>>>>SELECT *, 0 done FROM table where .... into #temp
>>>>:loopwhile rowCount>0
>>>>  update #temp
>>>>  set done=1
>>>>  , Top = coalesce(Top,T.Top)
>>>>  , ...
>>>>       FROM table T WHERE #temp.done=0 AND T.id = #temp.ParentID
>>>>:loopend
>>>>SELECT * FROM #TEMP
>>>>

>>>
>>>Hi Fabio,
>>>
>>>Thanks - I couldn't get this to work from your code (maybe I misunderstood something) but it gave me a direction. I'm testing the following at the moment. Do you see any problems ?
>>>

>>>CREATE PROCEDURE test  @id int AS
>>>SELECT * into #temp FROM Rects WHERE id=@id
>>>WHILE  @@ROWCOUNT  > 0
>>>BEGIN
>>>  UPDATE #temp
>>>  SET ParentID = T.Parentid
>>>   , rtop = coalesce(#temp.rtop,T.rtop)
>>>   ,rleft = coalesce(#temp.rleft,T.rleft)
>>>  ,rwidth = coalesce(#temp.rwidth,T.rwidth)
>>>  ,rheight = coalesce(#temp.rheight,T.rheight)
>>> , colour = coalesce(#temp.colour,T.colour,'')
>>>        FROM Rects T  WHERE  T.id = #temp.ParentID
>>>END
>>>SELECT * FROM #TEMP
>>>GO
>>>

>>>Regards,
>>>Viv
>>
>>

>>CREATE PROCEDURE test  @id int AS
>>SELECT * into #temp FROM Rects WHERE id=@id
>>WHILE  @@ROWCOUNT  > 0
>>  UPDATE #temp
>>  SET ParentID = T.Parentid
>>   , rtop = coalesce(#temp.rtop,T.rtop)
>>   ,rleft = coalesce(#temp.rleft,T.rleft)
>>  ,rwidth = coalesce(#temp.rwidth,T.rwidth)
>>  ,rheight = coalesce(#temp.rheight,T.rheight)
>> , colour = coalesce(#temp.colour,T.colour,'') -- WHY '' ?
>>        FROM Rects T
>>        WHERE  T.id = #temp.ParentID
>>              AND ( #temp.rtop IS NULL OR #temp.rleft IS NULL OR #temp.rwidth IS NULL ... #temp.rheight IS NULL )
>>
>>SELECT * FROM #TEMP
>>

>
>Hi,
>
>>WHY '' ?
>No reason. :-}I started with Borislav's code - it's just a vestigal trace. And you're right - there's no reason to continue once all columns have a value.
>
>Next problem: Is it possible to make this generic - i.e. code that will work against any table without knowing the column names or types (except id & ParentID)?
>

Sure, it is not very complex with VFP language,
but with MS-SQL 2000 it is not simple;
and it can become very inefficient.

A Trigger Update solution is a better solution for me.
You can write a "trigger builder" for implement this generic schema.

>Regards,
>Viv