Evaluating an Infix expression

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Evaluating an Infix expression

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-15 : 07:02:55

Hivo writes "I'll try to make this explanation simple, I have a table that looks like:


unit1    unit2    formula
--------------------------
m        km       a/1000.0
°C       °K       a+273.15

As you can see this is a table containing formulas for unit conversions. The formula can be very complicated though but it only contains a variable and this symbols: +,-,*,/,(,)

Along all the database I need to create views that implement the unit conversion formula. My first idea was to create a user defined function that takes 3 paramters (from_unit, to_unit, from_value) and returning the corresponding value.

I later realized that my formula can be easily evaluated directly by SQL just by replacing the variable "a" with the corresponging value and doing the query directly:

-- replace "a" with from_value and then construct the query
select value=(10.0/1000.0)

So I thought: "Great I don't have to go through all the infix-postfix conversion". Sadly I cannot use this inside a user defined function because I need to create a dynamic query and call the function exec('sql value=(10.0/1000.0)'). Obviously I need to do this because the query needs to be created depending on the input paramenters of the function.

Well, after some research I discovered that user defined functions cannot call Stored Procedures. So I'm kind of stuck with this problem. I have the feeling that there might be an easier or more efficient way of achieving this without going through all the infix-postfix conversion.

Can you guys give me hints, pointers or probably a solution to this problem?"

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-02-15 : 08:57:06

I am assumingm these formulas are fixed not variable.
So If there are not too many I would hard code them and use a case statement.

select

Conversionresult = case when @uom = 'km'
then = @Input1 / 1000
when @uom = 'K'
then @Input1 + 273.5
End

Jim
Users <> Logic

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-15 : 09:27:00

You won't vbe able to do this in a udf.
You might be able to do it in a view using openquery but why not call SPs rather than use views.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-15 : 10:20:06

this might do it for you... though you may experience some rounding issues:


CREATE FUNCTION dbo.EvalF
(
	@eq varchar(100),
	@a decimal(18,10)
) 
RETURNS decimal(18,10) AS  
BEGIN 

	Set @eq = replace(@eq,'a',@a)
	
	Declare @tally table (n int)
	Insert Into @tally
	Select n = n1.n + n2.n + n3.n + n4.n + n5.n + n6.n + n7.n + n8.n
	From 
		(Select n=0 Union Select 1) n1,
		(Select n=0 Union Select 2) n2,
		(Select n=0 Union Select 4) n3,
		(Select n=0 Union Select 8) n4,
		(Select n=0 Union Select 16) n5,
		(Select n=0 Union Select 32) n6,
		(Select n=0 Union Select 64) n7,
		(Select n=0 Union Select 128) n8
	Order By n
	
	Declare @workingTable table (eq varchar(100), val1 varchar(100), val2 varchar(100), bChar varchar(5), mChar varchar(5), aChar varchar(5), st int, md int, fn int, step int, calcVal varchar(100))
	
	Insert Into @workingTable 
	Select 
		eq = @eq, 
		val1 = substring(@eq,Z.st+1,Z.fn-Z.st-1), 
		val2 = substring(@eq,Y.st+1,Y.fn-Y.st-1), 
		bChar = substring(@eq,Z.st,1), 
		mChar = substring(@eq,Z.fn,1),
	--	bChar = substring(@eq,Y.st,1), 
		aChar = substring(@eq,Y.fn,1),
		st = Z.st,
		md = Z.fn,
		fn = Y.fn,
		1,
		null
	From
		(
		Select St = A.n, Fn = min(B.n)
		From (Select n, chr=substring(@eq,n,1) From @tally t Where substring(@eq,n,1) in ('+','-','*','/','(',')')) A
		Left Join (Select n, chr=substring(@eq,n,1) From @tally t Where substring(@eq,n,1) in ('+','-','*','/','(',')')) B
		On A.n < B.n
		Group by A.n
		) Z
	Inner Join
		(
		Select St = A.n, Fn = min(B.n)
		From (Select n, chr=substring(@eq,n,1) From @tally t Where substring(@eq,n,1) in ('+','-','*','/','(',')')) A
		Left Join (Select n, chr=substring(@eq,n,1) From @tally t Where substring(@eq,n,1) in ('+','-','*','/','(',')')) B
		On A.n < B.n
		Group by A.n
		) Y
	On Z.Fn = Y.St
	
	Declare @step int
	Set @Step = 0
	While @step<=10 and exists(Select * From @workingTable Where isnull(val1,'')<>'' and isnull(val2,'')<>'' and calcVal is null)
	Begin
		Set @Step = (Select max(step) From @workingTable)
	
		Update @workingTable
		Set calcVal = case
				when mChar = '+' then
					convert(decimal(18,10),val1) + convert(decimal(18,10),val2)
				when mChar = '-' then
					convert(decimal(18,10),val1) - convert(decimal(18,10),val2)
				when mChar = '*' then
					convert(decimal(18,10),val1) * convert(decimal(18,10),val2)
				when mChar = '/' then
					convert(decimal(18,10),val1) / convert(decimal(18,10),val2)
				Else 0 end
		From @workingTable
		Where isnull(val1,'')<>'' and isnull(val2,'')<>'' 
		and calcVal is null
		and step = @Step
	
		Select @eq = replace(@eq,bChar+val1+mChar+val2+aChar,calcVal)
		From @workingTable 
		Where step = @step
		and calcVal is not null	
		
		Insert Into @workingTable 
		Select 
			eq = @eq, 
			val1 = substring(@eq,Z.st+1,Z.fn-Z.st-1), 
			val2 = substring(@eq,Y.st+1,Y.fn-Y.st-1), 
			bChar = substring(@eq,Z.st,1), 
			mChar = substring(@eq,Z.fn,1),
		--	bChar = substring(@eq,Y.st,1), 
			aChar = substring(@eq,Y.fn,1),
			st = Z.st,
			md = Z.fn,
			fn = Y.fn,
			step = @step+1,
			null
		From
			(
			Select St = A.n, Fn = min(B.n)
			From (Select n, chr=substring(@eq,n,1) From @tally t Where substring(@eq,n,1) in ('+','-','*','/','(',')')) A
			Left Join (Select n, chr=substring(@eq,n,1) From @tally t Where substring(@eq,n,1) in ('+','-','*','/','(',')')) B
			On A.n < B.n
			Group by A.n
			) Z
		Left Join
			(
			Select St = A.n, Fn = min(B.n)
			From (Select n, chr=substring(@eq,n,1) From @tally t Where substring(@eq,n,1) in ('+','-','*','/','(',')')) A
			Left Join (Select n, chr=substring(@eq,n,1) From @tally t Where substring(@eq,n,1) in ('+','-','*','/','(',')')) B
			On A.n < B.n
			Group by A.n
			) Y
		On Z.Fn = Y.St
	End

	Return convert(decimal(18,10),@eq)

END

Test:


Declare @eq varchar(100),
	@a decimal(18,10)
Select @eq = '(((5/9)*(a-32))/3)'
Set @a = 65.78

Select @eq, @a, dbo.EvalF(@eq,@a)

result:


---------------------- ----------------- ----------------
(((5/9)*(a-32))/3)     65.7800000000     6.2555555561

(1 row(s) affected)

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources