Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use field content as field name

Status
Not open for further replies.

korinor

Technical User
Nov 3, 2004
4
DE
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
 
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
 
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
 
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
 
@ingresman

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

korinor
 
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 ?
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top