formulas in tables

[skypalae]

hello,

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
....


to understand what i want. i don't want 'any'solution. i found a 'solution' immediately.

make a recordset with textual form of the formulas

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

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

while (not eof) {
str += "SELECT %s AS x, %s AS y FROM shapes", rec.x, rec.y
rec.MoveNext
if (not eof)
str += "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.

(i've also posted this question to 'Ask an Expert' forum. i'm sorry, i'm new here and it was the first forum i found.)

[KnooKie]

you may wish to post here also...


www.sqlteam.com

[shankar_nr]

sorry but i do not have access 2000. If you can tell me this then I coudl help. Does access 2000 have the replace function. it is a function that finds a particular string and replaces it with something else. ofcourse you have a choice.

if it does, then you need to do something like this:

eval(replace(replace(types.x,"h",shapes.h),"w",sha pes.w))

and you can do the same with the y cordinate. What its going to do is, first it finds all the "h" and replaces it with a integer values h from shapes table. Then it does the same thing with the "w" parameter. so you will end up with a string and thus:
h function: eval("5 * 2/3")
w function: eval("-5/2")

where the h and w function is what you have typed in the x and y field of the types table.

hope this sounds english to you.

Neel!