Using Stored Math formula

[vimi4k]

Hi there !!!

I am using VS 2015 VB.NET and I want to use stored formulas to do maths operations with some database columns, please find below what exactly i want to do :

table1.column1 is having a stored expression i.e. (Basic * CustomDuty)

Now the text Basic and CustomDuty refers to columns in 2 different tables :
Table 'SaleInvoice' is having column named 'Basic' which contains the basic value of the sale invoice (say 10000)
Table 'Taxes' is having column named 'CustomDuty' which contains the rate of Custom Duty (say 5%)

I want to perform this multiply operation which is stored in text format to real columns in these tables.

Could you please guide how to parse the stored expression to real table columns in the database

thanks
VIMI

[DataMiser]

I'm not quite sure what you are really asking

[vimi4k]

@DataMiser, thanks for your reply

Sorry I didn't able to explain my problem, let me try again...

a formula to calculate Taxes is stored in text format in a database column that could be changed by the user

the stored formula text string will be like
Basic * CustomDuty
or
Basic * VAT
etc...

the words (Basic , CustomDuty, VAT) mentioned in this formula string are columns in 2 different tables
Table 'SaleInvoice' is having column named 'Basic' which contains the basic value of the sale invoice (say 10000)
Table 'Taxes' is having columns named 'CustomDuty' and 'VAT' which contains the rate of Custom Duty and VAT (say 5% and 12% respectively)

Now I want to execute the formula which is stored in a string format but will the real columns
i.e. SalesInvoice.Basic * Taxes.CustomDuty

I don't know how to execute a command with real database columns in reference with the formula stored in a text format

[DataMiser]

Well you would have to read the data in that field which contains your formula. You would then need to parse that out into its individual parts and create a routine that actually applies that method to the target fields.

That said I am not sure why you would even have a field like that in your DB. This should be in code or in a stored procedure. The only part of it that you should need in your db or config file would the tax rate.

In the end using a field like you are you will still have to write the code to do the actual calculations and you will have to write the code to determine what that calculation should use.

My advice would be to drop the field and write code to do the calculation either directly in your project or as a stored procedure in your db

[vimi4k]

@datamiser
Thanks for your reply
I cannot hard code the formula in the code or stored procedure as the formula keeps changing from time to time, in Indian statutory requirements the taxes are not always the same, the government changes the tax structure almost in every budget.

Will you please elaborate on
"Well you would have to read the data in that field which contains your formula. You would then need to parse that out into its individual parts and create a routine that actually applies that method to the target fields. "

how to parse the text to refer the columns in database, here i am confused.

[DataMiser]

Yes values change from time to time but the basic formula?

For example if I were coding a program the Tax Rate would be held in the data base the formula would be in code.

Tax=SubTotal*TaxRate

Total=subtotal+Tax

This should always be the same but of course the total will be different on every sale and the TaxRate could change at any time. What should not change is the you multiply one by the other.

If this is not the case then you would need to provide a better example of how this might change in order for me to be able to suggest a solution.

As for parsing what you now have you have to break it into the basic parts. This can be done using .Split()
You would then have to look at the 2nd part and pass it to a select case or something so you can do the correct operation on it as well as looking at the first and third part to see which fields they relate to.

You will end up with several lines of code where quite likely only 1 is needed.