execute string in a query

Question

Post reply

execute string in a query

Rakesh-467630

Valued Member

Points: 59
More actions
July 21, 2007 at 12:48 am

#117952

Hi All,
I'm using SQL Server 2000.
There are two tables in my DB. Consider following stucture...
1. Formula_mst
==============
FormulaName Formula
------------ -----------
Mutiply         col1*col2
Add             col1+col2
2. DataMst
==========
col1 col2
---- ----
5      10
I'm looking for output as under.
---------------------------------
FormulaName output
------------ --------
Multiply         50
Add             15
Can you please help me with the query?
Many thanks.
Rakbat

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select 'Multiply' As FormulaName, mutiply as Output from table

union all

Select 'Add' As FormulaName, Add as Output from Table

Michael Meierruth SSChampion Points: 10051 More actions · Answer 2

I don't understand Rakesh's problem of wanting to do elementary arithmetic using tables of operators and operands. I mean, why would you want to do that?

And I don't understand Ninja's solution, unless s/he has misunderstood the problem.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

How would this solution work??

Prasad Bhogadi
www.inforaise.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I just thaught that he wanted to take 2 columns into 2 rows... must have misread that problem.

Rakesh-467630 Valued Member Points: 59 More actions · Answer 5

Hi Michael,

I tried presenting the simplest form of structure to get the basic idea on query.

I got a fomula master in a table. And actual data in seperate table.

(As described in a structure in my earlier post)

Want a query which can read formula from formula master and get output from data table.

formula consits actual column names in data table.

For real working example consider this:

FormulaName Formula

====================

Profit After Tax Profit Before tax - Tax (this gives you PAT aka Profit After tax.)

Now I got around 100 odd formulas and have data in a seperate table and need the output.

Thanks

Rakesh

Michael Meierruth SSChampion Points: 10051 More actions · Answer 6

But to me it looks like you're dealing with a problem that's easier to solve using Excel. I don't see relational database concepts as being very useful here.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Let me put it this way. I've seen this done before using sql server. But the guy pretty much wrote a lite version of Excel. So I'd try to find a way around this one if possible.

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Rakesh,

where you formula come from?

Can users define formula?

Is there any fixed set of formula your system supports?

1st option is quite dangerous. It gives user a possibility to read data they do not suppose to see. And when it's happened they will point fingers on guess who?

2nd option may allow yo create set of UDF's and let users execute them.

CREATE FUNCTION ProfitAfterTax (

@ProfitBeforetax money, @tax money)

AS ....

or

CREATE FUNCTION ProfitAfterTax (

@OrderLineId int)

AS

...

@ProfitAfterTax = ProfitBeforetax - @tax

FROM dbo.OrderLine

WHERE OrderLineId = @OrderLineId

Actually I would not use any of these solutions.

I use completely different approach, but it's another story.

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

You got me on my curious side Sergiy, what is that other route you are talking about?

Sergiy SSC Guru Points: 110208 More actions · Answer 10

It starts from point "Data Dictionary".

_____________
Code for TallyGenerator

Rakesh-467630 Valued Member Points: 59 More actions · Answer 11

Hi Sergiy,

Formulas are in the one of the tables called formula_mst.

Yes admin users can define/edit the formula. Thats the basic reason I want it to run on the fly.

I had earlier thought of calculated columns and UDF's.

But if the users change the formula, I'll need to do the same in my UDF's

Many Thanks for helping out.

Regards

Rakesh

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 12

Looks like this isn't something that has to do with 'traditional' data.

The table with formulas is just a place to store formulas, instead of having them coded in some app.

The accountants here have a similar thing for the book keeping, and it actually works pretty well..

(belive me, those formulas are *not* to be toyed with... )

Basically, it's the same setup, formulas (or metadata if you will) are kept in tables, so they have an app (in this case something in VB) that reads that and generates the final SQL string that is then executed by the app (as any other normal SELECT statement). So you also need to first read out the formulas, parse it and put them together to a statement and then run it.

-- generate

select 'select [' + FormulaName + '] = ' + Formula + ' from #DataMst' from Formula_mst

-- run the generated statements (from somewhere)

select [Multiply] = col1 * col2 from DataMst

select [Add] = col1 + col2 from DataMst

-- results

Multiply

-----------

50

(1 row(s) affected)

Add

-----------

15

(1 row(s) affected)

..I'm not too sure that this is something that you'd want fully imbedded in T-SQL code (although it's possible)

If the formulas gets too many, or complex enough, the 'generator' should be as simple as possible.

/Kenneth