Algorithmic question

I have a table with a field that contains a currency quote as a float.
In another table I have a rule field as a varchar.
We have an application that uses both to gennerate a price.
I need to do the same thing but in SQL.
An example could be:
Price = 1.05
rule = '*100'
Another example:
price = 1.05
rule = '*0+95'
The rules can also include division and minus.
How do I get the value of the field with the rule applied in SQL
Any idears ?
Best regards
Mikaelwhen you select a query do you use a single rule for all the prices or a
different rule based on condition.
can you post the ddl please|||I've done something similar but on a very large scale for a client, they
have about 10 calculations each calculation has a couple of hundred
different variables with different formula's, the best approach I found was
dynamic SQL and parameter substition...
Point 1 though, you shouldn't use float because its an approximate datatype,
use decimal instead.
declare @.formula_sql nvarchar(1000)
declare @.rule nvarchar(50)
set @.rule = '* 0.95'
set @.formula_sql = 'set @.answer = @.price ' + @.rule
declare @.answer decimal( 28, 3 )
sp_executesql @.forumla_sql,
N'@.price decimal( 28, 3 ), @.answer decimal( 28, 3 )
output',
@.price, @.answer OUTPUT
print @.answer
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:817AE7F0-660E-4174-BAA3-6220184E6F60@.microsoft.com...
>I have a table with a field that contains a currency quote as a float.
> In another table I have a rule field as a varchar.
> We have an application that uses both to gennerate a price.
> I need to do the same thing but in SQL.
> An example could be:
> Price = 1.05
> rule = '*100'
> Another example:
> price = 1.05
> rule = '*0+95'
> The rules can also include division and minus.
> How do I get the value of the field with the rule applied in SQL
> Any idears ?
>
> --
> Best regards
> Mikael|||Mikael
First of all avoid using FLOAT datatype for currency quote you may get
incorrect result particular in calculation
http://www.aspfaq.com/show.asp?id=2477
For the problem , please visit at
http://www.sommarskog.se/dynamic_sql.html
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:817AE7F0-660E-4174-BAA3-6220184E6F60@.microsoft.com...
>I have a table with a field that contains a currency quote as a float.
> In another table I have a rule field as a varchar.
> We have an application that uses both to gennerate a price.
> I need to do the same thing but in SQL.
> An example could be:
> Price = 1.05
> rule = '*100'
> Another example:
> price = 1.05
> rule = '*0+95'
> The rules can also include division and minus.
> How do I get the value of the field with the rule applied in SQL
> Any idears ?
>
> --
> Best regards
> Mikael|||Tony solved my problem, but here is the DDL anyways:
CREATE TABLE #curr
(
[id] int not null identity(1,1) primary key,
[rule] varchar(100) not null
)
CREATE TABLE #Price
(
FK_CurrId int not null primary key,
[price] decimal(28,10) not null-- float
)
insert into #curr ([rule])
VALUES ('*0+102')
INSERT INTO #Price (FK_CurrId,[price])
VALUES (@.@.identity,100)
SELECT *
FROM #curr c
INNER JOiN #Price p ON c.id = p.FK_CurrId
Best regards
Mikael
"Omnibuzz" wrote:

> when you select a query do you use a single rule for all the prices or a
> different rule based on condition.
> can you post the ddl please|||>> I have a table with a field [sic] that contains a currency quote as a float [bad id
ea!]. In another table I have a rule field [sic] as a varchar. <<
Columns and fields are totally different concepts. Get a book on basic
RDBMS before you do any more programming. FLOAT is never used in
currency calculations. The EU publishes some rules that you have to
follow when computing with Euros. The US has the GAAP rules. Your
design will not pass an audit.
You are vague about these rules, but it looks like they are arithmetic
functions of the form (price * A + B) where A and B can be positive or
negative, A can be greater or less than zero, and B can be any number
within a range.
CREATE TABLE Rules
(rule_name CHAR(7) NOT NULL PRIMARY KEY,
multiplier DECIMAL (8,4) DEFAULT 1.0 NOT NULL,
adder DECIMAL (8,4) DEFAULT 0.0 NOT NULL);
SQL is a compiled language and you are trying to use it as if you were
writing BASIC on the fly. The kludge is to use dynamic SQL, to mimic
interpreted BASIC.
I saw a similiar solution which stored formulas in a string for dynamic
SQL. A missing decimal point converted the formula to integer math and
destroyed the data integrity. It is really hard to see that 2 and 2.0
are not the same when you have a table with hundreds of such strings.sql