formulas in tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

formulas in tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-01 : 06:58:32

skypalae writes "hello,

i've posted this question to Database Journal (http://forums.databasejournal.com/showthread.php?s=&postid=76614#post76614) and the advice i got is to ask here at SQLTeam. i've looked at your previously answered questions, but there are so many and even search engine showed no answer. i'm sorry if i post the question to the wrong place (according to your first sentence "We are answering questions about SQL server only") but it is more general SQL than a concrete implementation.

i'm using VC++ and MS Access 2000 and i have this problem

i've created two tables. one containing information about different shape types and the second one containing information about concrete shapes. and i want to apply the rules of the types to the shape specific data. i'll give you very simple example

TABLE shape_types (id VARCHAR, index INTEGER, x VARCHAR, y VARCHAR) ;

'triangle', 1, '0', 'h * 2/3'
'triangle', 2, '-w/2', '-h * 1/3'
'triangle', 3, 'w/2', '-h * 1/3'
'rectangle', 1, '-w/2', '-h/2'
'rectangle', 2, '-w/2', 'h/2'
'rectangle', 3, 'w/2', 'h/2'
'rectangle', 4, 'w/2', '-h/2'

TABLE shapes (name VARCHAR, type VARCHAR, w NUMBER, h NUMBER)

'sq20', 'rectangle', 20.0, 20.0
'rq10*30', 'rectangle', 10.0, 30.0
'tr5', 'triangle', 5.0, 5.0

i thought that when i want to recieve all the coordinates for the 'sq20' shape i would make a querry like

SELECT index, EVAL(x), EVAL(y)
FROM shape_types, shapes
WHERE type=id AND name='sq20'

and the result would be
1, -10.0, -10.0
2, -10.0, 10.0
3, 10.0, 10.0
4, 10.0, -10.0

but Access returns this:
1, #Error, #Error
2, #Error, #Error
....

well, to understand me, i'm not looking for 'any' solution. i've found a 'solution' almost immediately construncting a query based on results from recordset.

make a recordset with textual form of the formulas

SELECT index, xFormula, yFormula
FROM shape_types, shapes
WHERE type=id AND name='sq20'

and then construct second query using formulas for each line separately (i use sort of pseudocode here, because it is more understandable)

while (not eof) {
query += "SELECT %d, %s AS x, %s AS y FROM shapes", rec.index, rec.xFormula, rec.yFormula
rec.MoveNext
if (not eof) query += "UNION"
}

but i don't like this solution. it's not that elegant and i suppose the union command takes more memory and more time (the shapes can be quite complex, more vertices, not only width/height but more parameters. the examples here are really informative).

please help me if there is a simple sql solution of this.

thank you."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-01 : 07:03:44

quote:
but i don't like this solution. it's not that elegant and i suppose the union command takes more memory and more time

The UNION operation will not slow you down nearly as much as the rec.MoveNext will.

Gotta admit I haven't seen what you're trying to do before, and I don't think it will work, but see if this helps:

SELECT index, EVAL(Replace(x, 'h', shapes.h)), EVAL(Replace(y, 'w', shapes.w))
FROM shape_types, shapes
WHERE type=id AND name='sq20'

The EVAL function can't see the VALUES that h and w represent, because the formula only contains the variables. The Replace function will change them to actual numbers and should let EVAL work correctly.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-01 : 10:04:05

This is actually an easy one, you just need to model your tables differently. I have used this technique in modelling, budgeting and other applications.

instead of storing this:

'triangle', 1, '0', 'h * 2/3'
'triangle', 2, '-w/2', '-h * 1/3'
'triangle', 3, 'w/2', '-h * 1/3'
'rectangle', 1, '-w/2', '-h/2'
'rectangle', 2, '-w/2', 'h/2'
'rectangle', 3, 'w/2', 'h/2'
'rectangle', 4, 'w/2', '-h/2'

create your table like this:

ShapeID
Index
H_Numerator
H_Denominator
W_Numerator
W_Denominator

Normally, I'd have 1 multiplier, but since you have many numbers like 1/3 and such I've decided to store 2 values per shape / dimension.

Now just populate your table like so:

'triangle', 1, 0,0, -2, 3
'triangle', 2, -1,2,-1,3
'triangle', 3, 1,2,-1,3
'rectangle', 1, -1,2, -1,2
'rectangle', 2, -1,2,1,2
'rectangle', 3, 1,2,1,2
'rectangle', 4, 1,2,-1,2

Hopefully you can see where I am coming at. If there are potentially other operations like adding or subtracting from the results, add more columns.

Then, query would like something like this:

select Shape, w * W_numerator / W_Denominator,
h * H_Numerator / H_denominator
from
Shapes
Inner join
ShapeData
on .....

If your formulas were more complex, you might store more data -- but your must keep it abstract and include all variations as your data.

So, you may have:

select Shape, w * W_numerator / W_Denominator + W_Adjustment
h * H_Numerator / H_denominator + H_Adjustment

if the formulas were in the form of 1/2w + 5 or something like that.

I hope this makes sense and helps. You will see that it is EXTREMELY powerful and flexible, and you don't need dynamic SQL or unions or anything. It should be very efficient.
(yes, I am quite proud of this technique).

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources