Re: Formula Parsing

Mike (michael.matthys@xxxxxxxxxxx) writes:
> In this structure the user can add as many data fields to a customer as
> he wants (e.g. Country, City, Email, Phone, ...). I have added triggers
> which create a view similar to a pivot (I am working in SQL 2000) and
> add triggers to the view so it is insertable, deletable and updateable.
>
> What I would like to do, is allow the user to create new fields where
> the values are based upon a calculation. This calculation would be done
> through a formula similar to what he would do e.g. in excel (this
> formula is stored in the dimFormula field then).
>...
> For example. Let's assume, the user wants to add a rating (field called
> 'Rating') to his customers based upon the result of 'AvgSales. He
> enters the formula 'if([AvgSales] > 2500, 'A', 'B')'.

I can only echo "ZeldorBlat" don't do this in SQL. If you had been on
SQL 2005, you could possibly have used CLR modules for the task.

But I wonder if you are not barking up the wrong tree entirely. Have
you looked at Analysis Services? I'm completely ignorant about Analysis
Services myself, but I would not be surprised if it has some support
for what you are trying to do.

If you are dead set on doing this in SQL 2000, you have to choices:
1) require that the user uses T-SQL syntax, for instance
CASE WHEN [AvgSales] THEN 'A' ELSE 'B' END
2) Define you own forumla language, and parse it in client code and
define the columns in the views as the users defines his formulas.

Beside AS, you could also investigate what 3rd party products out
there that may address your needs.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: OOP - a question about database access
    ... >>and project so much better in SQL DBMSes than in ODBMSes, ... >>100x more bytes from the database, just because you want your objects to ... > a related invoice.. ... > assoication from the customer to the invoice collection and have done ...
    (comp.object)
  • Re: object databases
    ... Now, tell me, you can do all that with 2 lines of code with SQL ... didn't provide the Customer, Order and OrderDetail classes. ... If you try to map classes to tables, ...
    (comp.object)
  • Re: user wants access
    ... I would strongly recommend you read Books Online for this information. ... the db_securityadmin fixed database role. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.server)
  • RE: search phrase
    ... What you might want to try is using the SQL Server 2000 XML thersoursa files ... After the Full Population is complete, run the following SQL FREETEXT queries ... "Yair Nissan" wrote: ... Remove all non alphanumeric characters from the customer name. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Protect and invirible Database from each other
    ... There is no way currently to stop them from seeing the other databases. ... Andrew J. Kelly SQL MVP ... > Each databases are belonged to each customer domains in this Server. ... > My problem is that, after customers connect to the SQL Server, they can ...
    (microsoft.public.sqlserver.security)