Use field content as field name

[korinor]

Hi,

is it possible in mySQL (or SQL generic) to use a field content as a field name?

Example:
I have created a database with a couple of fields which are named 1, 2, 3 and so on up to 14. It is a special calculation table.

Also there is a field called "reference" which contains a number between 1 and 14. This number tells me which field I should use for further calculation.

Does someone know a solution for this?
Thanks in advance.

korinor

 

[Bastien]

A name may consist of alphanumeric characters from the current character set, `_', and `$'. The default character set is ISO-8859-1 (Latin1). This may be changed with the --default-character-set option to mysqld. See section 5.8.1 The Character Set Used for Data and Sorting.

A name may start with any character that is legal in a name. In particular, a name may start with a digit; this differs from many other database systems! However, an unquoted name cannot consist only of digits.

You cannot use the `.' character in names because it is used to extend the format by which you can refer to columns (see section 10.2.1 Identifier Qualifiers).

Bastien

Cat, the other other white meat

 

[korinor]

Yeah, you're right, the sample is not really coming from a mySQL table. In fact it is a Access 2003 table where "digits only" are allows as field names.

Please think of these fields as a1, a2, a3 to a14 in order to comply with mysql field name rules.

In particular I only need to know how I use field content as "reference" to a field name.

korinor

 

[ingresman]

I might have got this wrong but....
select fieldnumber from reference into $fn;
$qry="select " . $fn . " from whatever";
execute($qry);
I think all you need to do is create dynamic SQL.
If you need a constant name for the field you can use the as modifer e.g.
select 1 as thecolumn from fred;
or
select 2 as thecolumn from fred;
Subsequent code can use thecolumn with no problems

 

[korinor]

@ingresman

I don't recognize this syntax (... into $fn).
Is this a special SQL feature or VBA?

korinor

 

[ingresman]

no it's just a bit of psuedo code,
you need to get the reference item into a variable and use this as the column name in the main sql.
What language are you attempting to use ?
Am I correct in the assumption i made ?

 

[korinor]

hmmm, pseudo-code, I understand... This means that it won't be possible in pure SQL (or at least very very very complicated).

Ok, I think I have found what I need to know. Thanks for your help.

korinor

 

[ingresman]

its possilble in fact easy !, can you show your solution so everyone can share please.
I use psuedo code as I don't know your target language and most people like to code it up anyway.