Evaluating String Arithmetic Expressions In A View
-
I Need Help, Please.
I am trying to use a math formula stored as a string in a calculation in a report writer that cannot evaluate the string formula so I am trying to resolve the formula in a database view before passing to the report writer.
I started by using a cast as float function until I ran into the divide formula and received an error “converting varchar to float”.
What I need is for the formula ‘96/(3*4)’ to resolve to 8.
I would prefer the conversion be part of a view but I could also do a update to the original table user defined column if the view option is not possible.
My data is very basic; I have a multi column primary key, BASE, LOT, PIECE (varchar(30)) and a FORMULA (varchar(80))
CREATE TABLE #F (
BASE VARCHAR(30),
LOT VARCHAR(30),
PIECE VARCHAR(30),
FORMULA VARCHAR(80))
INSERT INTO #F SELECT 'TEST5','1','10','1/2'
INSERT INTO #F SELECT 'TEST5','1','20','96/(3*4)'
INSERT INTO #F SELECT 'TEST5','1','30','36*12'
INSERT INTO #F SELECT 'TEST5','1','40','36.00*12.00'
INSERT INTO #F SELECT 'TEST5','1','50','15'
select * from #F
select base, lot, piece, cast(formula as float) from #f
Thanks in advance for any assistance.
-
First of all welcome. Great job posting some consumable data and information.
You have a couple issues with this. You can't convert your string formula to float. That is no different than trying to convert 'IsThisAFloat' to a float. You will have to run dynamic sql in order for this to work. This also means you can't do this in a view. You could however do it in a stored procedure. You also are likely to run into some issues with some of the sample formulas you posted. for example 1/2 in sql server does not return .5 it returns 0 because it is integer math. 1/2.0 would return what you would be looking for.
I am pretty swamped at work today but I will check in on this later this afternoon and see if you need some help or if somebody else has jumped in. Hopefully you can figure out a direction to go.
Here is a quick example of running some dynamic sql that would calculate your formula.
declare @sql varchar(50)
set @sql = '36.00*12.00'
exec('select ' + @sql)
Hope that helps at least to get you started.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hi Doug,
I love this sort of problem 🙂
The difficulty with the situation as presented is that each formula is a distinct calculation. Dynamic SQL is the right way to go, but we need to solve a few problems first. Ideally, I'd like to wrap this up into a function, but we can't use dynamic SQL in a function. So, I've had to use a cursor/loop based solution. Not ideal, but as far as I can see (and I'm happy to be corrected on this if anyone else can see a set based solution) the only way to get the result you want.
This solution uses a tally table, so we'll build that first:
create table dbo.Tally (n int)declare @i int
set @i = 1
while @i <=1000
begin
insert dbo.Tally values (@i)
set @i=@i+1
end
create clustered index ix_n on dbo.Tally(n)
Now the fun bit - converting your inputs so that all of the numbers are cast to float. This function works for your data as presented. There may be situations where it produces errors, so you should test all eventualities:
if isnull(object_id('dbo.udf_Build_Formula'),0) <> 0 drop function dbo.udf_Build_Formula
go
create function dbo.udf_Build_Formula (
@maths_string nvarchar(100)
)
returns nvarchar(1000)
as
begin
declare @sql nvarchar(1000)
, @params nvarchar(1000)
, @converted_string nvarchar(1000)
----------------------------
-- convert numerics to float
----------------------------
-- splits the input into single characters, then uses logic to decide if we need a cast operation
-- explicitly set the collector string to empty to avoid concat null yields null later
set @converted_string = ''
-- join with tally table and substring to isolate individual characters
; with cte as (
select n, substring(@maths_string,n,1) as TextCharacter
from dbo.Tally
where n <= len(@maths_string)
)
-- convert the single characters
, cte2 as (
select a.n
, case
-- if this character is a number and the previous character is not a number or decimal point, add a cast
when patindex('[0-9]',a.TextCharacter) = 1
and patindex('[0-9]',isnull(b.TextCharacter,'$')) = 0
and isnull(b.TextCharacter,'$') <> '.'
then 'cast(' + a.TextCharacter
-- if this character is not a number (and not a decimal point) and the previous character is, add a closing bracket
when patindex('[0-9]',a.TextCharacter) = 0
and patindex('[0-9]',isnull(b.TextCharacter,'$')) = 1
and a.TextCharacter <> '.'
then ' as float)' + a.TextCharacter
-- otherwise, just take the value
else a.TextCharacter
end as CasedCharacter
from cte a
left join cte b
on a.n = b.n+1
)
-- use the undocumented rollup operation to recombine the result into a single string
select @converted_string = @converted_string +
-- if the last character is a number, add a closing cast
case when cte2.n = len(@maths_string)
and patindex('[0-9]',right(cte2.CasedCharacter,1)) = 1
then cte2.CasedCharacter + ' as float)'
else cte2.CasedCharacter
end
from cte2 order by n
-- return the result
return @converted_string
end
-- return the results
select *
, dbo.udf_Build_Formula (Formula)
from #F
Now it's RBAR all the way as we calculate the values:
-- because each row of input is a separate calculation-- we need to process row by row in a cursor
-- create a table to hold the results
create table #f2 (
base varchar(30)
, lot varchar(30)
, piece varchar(30)
, formula varchar(80)
, convertedformula nvarchar(1000)
, result float)
-- declare some working variables
declare @base varchar(30)
, @lot varchar(30)
, @piece varchar(30)
, @formula varchar(80)
, @convertedformula nvarchar(1000)
, @sql nvarchar(1000)
, @params nvarchar(1000)
, @result float
declare @fetch int
set @fetch = 1
-- declare our cursor
declare cur_Table_Output cursor
for
select *
, dbo.udf_Build_Formula (Formula)
from #F
-- fetch and loop
open cur_Table_Output
fetch next from cur_Table_Output
into @base
, @lot
, @piece
, @formula
, @convertedformula
set @fetch = @@fetch_status
while @fetch = 0
begin
-- calculate the result via dynamic sql
set @sql = N'select @out = ' + @convertedformula
set @params = N'@out float output'
exec sp_executesql @sql, @params, @out = @result output
-- add a row to the output table
insert #f2
values (@base
, @lot
, @piece
, @formula
, @convertedformula
, @result
)
-- fetch the next row
fetch next from cur_Table_Output
into @base
, @lot
, @piece
, @formula
, @convertedformula
set @fetch = @@fetch_status
end
-- tidy up
close cur_Table_Output
deallocate cur_Table_Output
-- view the results
select * from #f2
My guess is it will perform like a dog, but do what you expect it to.
Hope it helps!
Regards, Iain
Edit: Removed unused variables from function
-
SSCrazy
Thanks for your assistance.
Based on your suggestion I was able to create an update script that I am running as a sql job.
While it works it is a bit slow (39 seconds for 3200 rows) and isn't a real time results but is instead a periodic update based on the sql job schedule (every 15 minutes).
I was able to come up with what I think is a simple solution for the integer division problem using a replace function.
Replace any "/" symbol with "/ 1.0000 /".
Any number divided by 1 will always result in that number and adding the decimal to the 1 removes the integer functionality from the computation.
For my example of 1/2 = 0 (should be 0.5) the resulting formula would be 1/ 1.0000 / 2 = 0.5000
I ran through many formula samples using the / 1.0000 / and everyone appeared to compute correctly.
Please let me know if you think I overlooked something?
Here is the script I am using (real table and column names are used)
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ISNULL(@SQL+CHAR(13),'')
+ 'UPDATE dbo.REQUIREMENT SET user_2 = ' + REPLACE(DIMENSIONS,'/','/1.0000/') + ' WHERE DIMENSIONS = ' + QUOTENAME(DIMENSIONS,'''')
FROM dbo.REQUIREMENT where WORKORDER_TYPE = 'Q' and DIMENSIONS is not null
--PRINT @SQL
EXEC (@SQL )
GO
I haven't tried my hand at any stored procedures yest and I am not sure if I can call that procedure in a view or directly into my report writer.
-
Iain
Thanks for your suggestion!
I will give it a try next week and let you know how I make out.
irobertson (9/12/2011)
Hi Doug,I love this sort of problem 🙂
This solution uses a tally table, so we'll build that first:
Now the fun bit - converting your inputs so that all of the numbers are cast to float.
My guess is it will perform like a dog, but do what you expect it to.
Hope it helps!
-
I haven't tried my hand at any stored procedures yest and I am not sure if I can call that procedure in a view or directly into my report writer.
No you can't do this in a view. A view is nothing more than a stored select statement (ok there are some more advanced things you can do with them, but it won't help you here). A view can't do processing. A view can't run dynamic sql. You could create a stored proc that selects your calculated results and make that the datasource for your report.
I would agree with CELKO...
SQL is not a spreadsheet language. It is meant to do DATA retrieval and integrirty only. We can force it with kludges and dynamic SQL, etc. but that is like pounding screws in furniture with a rock; it is the wrong tool. You need a screw driver.
What you are doing is possible but the approach you are taking is prone to errors and horrible performance. Maybe you could keep your formula column but also add a Result column that you could calculate from the formula when you insert the data? That way you could perform the calculation client side prior to inserting. That way you don't have all this challenging and performance destructive processing in sql. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Celko
My problem is that my ERP system stored a formula in sql as a string. Our ERP program uses C and does not have a problem using the formula for calculations.
My problem is that I need to use the value in a report as part of a quantity calculation so I am stuck resolving the formula and my report writer, Crystal Reports, can not compute the formula.
Typically when I have data issues like joins or case options I create a SQL view to pull the data and then use that view as the data source for the report.
Any suggestions would be appreciated.
CELKO (9/13/2011)
SQL is not a spreadsheet language. It is meant to do DATA retrieval and integrity only. We can force it with kludges and dynamic SQL, etc. but that is like pounding screws in furniture with a rock; it is the wrong tool. You need a screw driver. -
dougb 34607 (9/15/2011)
CelkoMy problem is that my ERP system stored a formula in sql as a string. Our ERP program uses C and does not have a problem using the formula for calculations.
My problem is that I need to use the value in a report as part of a quantity calculation so I am stuck resolving the formula and my report writer, Crystal Reports, can not compute the formula.
Typically when I have data issues like joins or case options I create a SQL view to pull the data and then use that view as the data source for the report.
Any suggestions would be appreciated.
CELKO (9/13/2011)
SQL is not a spreadsheet language. It is meant to do DATA retrieval and integrity only. We can force it with kludges and dynamic SQL, etc. but that is like pounding screws in furniture with a rock; it is the wrong tool. You need a screw driver.This is why I suggested storing the result of the formula. It is a lot easier to deal with this when in the front end than in the database. If you are unable to modify the front end then you will be left with dynamic sql. Depending on the reporting tool you should be able to use a stored proc as your datasource just like you would with a view.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
dougb 34607 (9/15/2011)
My problem is that I need to use the value in a report as part of a quantity calculation so I am stuck resolving the formula and my report writer, Crystal Reports, can not compute the formula.Crystal Reports can use a stored procedure as a source, and the stored procedure can use dynamic SQL to compute the formula.
I no longer have Crystal Reports installed anywhere, so I can't give you further details.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Credit to Lowell for the photo.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Why not implement a CLR function to evaluate this? Give it a formula, and it returns the value (cast into some standard type). CLR will give you a LOT more ability to a. express items in more intricate functions, b. better error checking and control, and c. a LOT fewer possible issues with ugly things like SQL injection (yes - you just opened that door here, so you'd have to have some way to safeguard it).
The function can be called from the view. the view and be used in the report.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply