Select Row_Number() over (order by (select 0)) as rowID,replace(replace(replace(Formula,'a',a),'b',b),'c',c) as MyMath,* 
into #TMP
from
(
Select 'a+b+c'as formula,12 as a,2 as b,3 as c Union all
Select 'a+b-c',12,2,3
) a


Declare @StrSql varchar(100),@MyCount int
set @MyCount = 1

while @Mycount <= (select count(*) From #TMP)
begin
	select @StrSql = 'Select ' + MyMath from #TMP where RowID = @MyCount
	exec(@StrSql)
	set @MyCount = @MyCount + 1
ENd
drop table #TMP

Select Row_Number() over (order by (select 0)) as rowID,replace(replace(replace(Formula,'a',a),'b',b),'c',c) as MyMath,* 
into #TMP
from
(
Select 'a+b+c'as formula,12 as a,2 as b,3 as c Union all
Select 'a+b-c',12,2,3
) a


Declare @StrSql varchar(100),@MyCount int
set @MyCount = 1
Create table #Results(RowID int,Result decimal(12,2))
while @Mycount <= (select count(*) From #TMP)
begin
	select @StrSql = 'Select ' + Convert(varchar(10),@MyCount) + ', ' + MyMath from #TMP where RowID = @MyCount
print @StrSQL
	Insert Into #Results(RowID,Result)
	exec(@StrSql)
	set @MyCount = @MyCount + 1
ENd

Select * from
#TMP a
inner Join
#Results b
on a.RowID =b.RowID

drop table #TMP
Drop table #Results

quote:

---

Originally posted by waterduck

@.@ very curious to TG solution

---

Hope can help...but advise to wait pros with confirmation...

---

Declare @Operations table (OperationID int, Description varchar(20))
Insert INto @Operations(OperationID,Description)
select 1,'Addition' Union All
select 2,'Subtraction' Union All
select 3,'Multiply'Union All
select 4,'Divide'


Declare @MyValues table(ValID int,SequenceID int, MyVal Decimal(30,5),FormulaID int)
Insert INto @MyValues(ValID,SequenceID,MyVal,FormulaID )
select 1,1,22.5,1 Union All
select 1,2,80.5,1 Union All
select 1,3,97.782,1Union All
select 2,1,12345.55,2 Union All
select 2,2,12345.55,2 Union All
select 2,3,12445.55,2 Union All
select 2,4,155.55,2 Union All
select 2,5,123495.55,2


Declare @MyFormula table (FormulaID int,SequenceID int,OperationID int)
Insert INto @MyFormula(FormulaID,SequenceID,OperationID)
select 1,1,1 Union All
select 1,2,3 Union All
select 1,3,4 Union All
select 2,1,1 Union All
select 2,2,1 Union All
select 2,3,1 Union All
select 2,4,1 Union All
select 2,5,4 

Declare @MYWORKINGTABLE table (WorkTableID int Identity(1,1) Primary Key Clustered,ValID int,SequenceID int,OperationID int,Description varchar(50),MyVal decimal(30,5),NewValue decimal(30,5))
Insert Into @MyWorkingTable(ValID,SequenceID,OperationID,Description,MyVal)
SElect a.ValID,a.SequenceID,c.OperationID,c.Description,a.MyVal
From
@MyValues a
Inner Join
@MyFormula b
on a.FormulaID = b.FormulaID
and a.SequenceID = b.SequenceID
Inner Join
@Operations c
on b.OperationID = c.OperationID
order by a.ValID,a.SequenceID

Declare @Anchor int, @MyNewVal decimal(30,5), @PriorvalID int
set @Anchor = 0 
set @MyNewVal = 0 
set @PriorValID = 0
Update a
set @Anchor = WorktableiD
,@MyNewVal = a.NewValue = case 
						when @PriorValID = ValID and OperationID = 1 then convert(decimal(30,5),@MyNewVal + a.Myval)
						when @PriorValID = ValID and OperationID = 2 then convert(decimal(30,5),@MyNewVal - a.Myval)
						when @PriorValID = ValID and OperationID = 3 then convert(decimal(30,5),@MyNewVal * a.Myval)
						when @PriorValID = ValID and OperationID = 4 then convert(decimal(30,5),@MyNewVal / a.Myval)
						else a.MyVal
					  end
,@PriorValID = a.ValID
from @MyWorkingTable a

Select *
from @MyWorkingTable a

Select valID,NewValue as FinalValue
from
(Select row_Number() over (Partition by a.ValID order by sequenceID desc) as RowID,*
from @MyWorkingTable a
) aa
where aa.RowID = 1

quote:

---

Originally posted by Transact Charlie

Sounds like you *might* be able to do that in a recursive CTE...... (evaluating expressions from the inside out)

Care to publish a little example or is it a trade secret / too much effort?

Charlie

---

quote:

---

Originally posted by TG

quote:

---

Originally posted by Transact Charlie

Sounds like you *might* be able to do that in a recursive CTE...... (evaluating expressions from the inside out)

Care to publish a little example or is it a trade secret / too much effort?

Charlie

---

probably could be done with cte but at this point I don't think it would buy me anything to switch other than about two weeks of regression testing.

Regarding the example, I looked for (but couldn't find) my little "proof of concept" testing code. I think its long gone. To post a working sample would be too much effort. The current code requires about 30 dependent opbjects and forumla data as well as the value data tables from too many sources (across multiple databases). It may violate some trade secrets but probably not - because the value is not so much the technology but rather all the company data.

Be One with the Optimizer
TG

---