Is there a way to dynamicaly create objects from data values ?

[#1]

I have a database with this architecture :

PRO ( CODPRO, NOMPRO, CODZN1, CODZN2... CODZN16)

TBL (CODTBL, CLETBL, NOMTBL, LIB1, LIB2)

In the CODZNxx fields of the table PRO, I find "generic" data. I mean those values names, types and list depends of a record in the TBL table.

SELECT LIB1, LIB2 FROM TBL WHERE CODTBL = 'zon' AND CLETBL = 'PRO0xx' (where xx is the the same as in CODZNxx) gives me in LIB1 : The CODZNxx friendly name, and LIB2 the CODTBL where I'll find the list of values I can use in the CODZNxx field.

Example :

SELECT CODZN1 FROM PRO WHERE CODPRO = '000001'

=> CODZN1 = 'FR'

SELECT LIB1, LIB2 FROM TBL WHERE CODTBL = 'zon' AND CLETBL = 'PRO001'

=> LIB1 = 'Manufacturing country' / LIB2 = '002'

SELECT LIB1, LIB2 FROM TBL WHERE CODTBL = '002' AND CLETBL = 'FR'

=> LIB1 = 'France' / LIB2 = 'République française'

This way, I am able to say Manufacturing country of product 000001 is France, instead of CODZN1 of product 000001 is FR.

I can do the job manually for all CODZNxx in my database. But this database is the same for several customers, so I'd like to find a way to automate the creation of LIST objects which allow me to filter the products using friendly name and values of the CODZNxx fields.

For some customers, CODZN1 will be the manufacturing coutry, for some others, it will be a flag to make the product visible on the website, for others it will be the energy score of the product, etc. and I'd prefer to automate this rather than re-creating each time all the list items.

Is there a way to do this ? I looked arround, but I can't find how to do this.

[Oleg_Troyansky]

You can build your "LOAD" statement as a string and assign it to a variable, and then execute the statement using $() - expansion. FOr example:

LET vLoad = 'SELECT A, B, C FROM ...';

$(vLoad)

Word of caution - you might run into problems with single quotes inside of your string. Sometimes you'll have to concatenate a CHR(39) instead of simply typing a single quote...

[#3]

Thank you.

My main problem isn't really the SQL query building. Even without variables, I should be able to deal with the data structure.

What I'm looking for is a macro or something like that, which will create my list objects correctly named according the TBL values.

[#4]

I mean something like that (pseudo-code)

SET posX = 50;
SET posY = 10;
SET Width = 150;
SET Height = 100;

for i = 1 to 16
Codzn$(i)Values:
LOAD
SQL select tbl1.cletbl, tbl1.lib1
from tbl tbl1
inner join tbl tbl2 on tbl2.lib2 = tbl1.codtbl
where tbl2.codtbl = 'zon' and tbl2.cletbl = 'PRO0$(left('0' & i, 2))';

Codzn$(i)Name:
SQL select tbl.lib1 from tbl where codtbl = 'zon' and cletbl = 'PRO0$(left('0' & i, 2))';

CreateListObject(Sheet1, Posx, Posy, Width, Height, Codzn$(i)Name, Codzn$(i)Values);
LET PosY = PosY + 150;
next