| Author |
Topic |
|
Kytro
Starting Member
9 Posts |
 Posted - 2005-09-07 : 20:44:13
|
I am trying to parse a calculation for a view.The view has 3 fields F1, F2 and Calc.F1 and F2 are numeric and Calc is varchar.I am trying to parse the calculation: F1 Calc F2Here is some sample data:F1 | F2 | Calc---------------10 20 +1 2 /12 4 * What I want to do is create another field Result that gives me 30, 0.5 and 48.I have to convert the whole lot to varchar, such as "10 + 20", but then SQL cannot covert it back to numeric.How can I parse the calculation? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-07 : 20:53:50
|
| I don't want to say "you can't", but it's highly unlikely you'll find a practical way to do this in SQL Server. SQL is not a full-featured programming language like C/C++ or Java, it is specifically designed for data manipulation and retrieval, and only supports the most basic programming constructs.Without more detail on what you're trying to do (and why you're doing it), I'd recommend NOT trying to store the data the way you're doing it now. |
 |
|
|
Kytro
Starting Member
9 Posts |
Posted - 2005-09-07 : 21:02:10
|
quote:
Originally posted by robvolk
I don't want to say "you can't", but it's highly unlikely you'll find a practical way to do this in SQL Server. SQL is not a full-featured programming language like C/C++ or Java, it is specifically designed for data manipulation and retrieval, and only supports the most basic programming constructs.Without more detail on what you're trying to do (and why you're doing it), I'd recommend NOT trying to store the data the way you're doing it now.
Basically the F1 and F2 represent two two other parameters. The parameters are stored in a table and are created by the user in order to define what is being measured. There are two types of parameter. Basic and calculated. The basic parameters have their result data provided from the user or other table. The calculated parameters are to allow users too make a new parameter based on other parameters.One parameter might be Hours and Another rate, and this data provided by the users. In order to get an Amount (rate*value) this information needs to be stored.Unless there is another way to provide users with the ability to define a parameter? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-07 : 21:08:40
|
| I suppose you can use the structure you have to STORE the parameter definition, but it will be nearly impossible to have SQL Server calculate it. You would need to have another application read the data and evaluate the expression for the user. |
|
|
|
Kytro
Starting Member
9 Posts |
Posted - 2005-09-07 : 21:38:00
|
quote:
Originally posted by robvolk
I suppose you can use the structure you have to STORE the parameter definition, but it will be nearly impossible to have SQL Server calculate it. You would need to have another application read the data and evaluate the expression for the user.
Perhaps I can limit the calculation types to +, *, /, - and then use a CASE to determine the calculation, but I was trying to avoid this.Additionally it will get more complex for the division calculation when I want to SUM a period of time.  |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-07 : 21:46:23
|
| I'm still not clear why you need or want to do this in SQL Server. I cannot think of a language less suited to accomplishing this goal...it's just not designed to support it. I can't help but think you'd be far better off pushing the data to another application, written in a language that can do this kind of thing, than waste time chasing this down in SQL. |
|
|
|
Kytro
Starting Member
9 Posts |
Posted - 2005-09-07 : 22:29:26
|
quote:
Originally posted by robvolk
I'm still not clear why you need or want to do this in SQL Server. I cannot think of a language less suited to accomplishing this goal...it's just not designed to support it. I can't help but think you'd be far better off pushing the data to another application, written in a language that can do this kind of thing, than waste time chasing this down in SQL.
It is basically due moving functioanlity from a legacy application. I may use MSAccess to do the calculation (the front end data will be in access) as I think it understands it may be able to do it more easily.My problem is more trying to work out what to do where as I am self taught and have only been using SQL server for 6 months on and off. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-09-08 : 08:50:45
|
there is always this.. but I think you'd have to loop through the records to calculate...Declare @a float, @b float, @op varchar(100)Set @a = 10.0Set @b = 20.0Set @op = '/'Declare @dynamicSQL nvarchar(1000), @result floatSet @dynamicSQL = 'Select @Result = convert(float,' + convert(varchar,@a) + ')' + @op + 'convert(float,' + convert(varchar,@b) + ')'exec sp_executesql @dynamicSQL, N'@result float OutPut', @Result OutputSelect @dynamicSQL, @result Corey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."  |
|
|
|
|
Parse a calculation