Your best bet would be to dynamically build the field string using whatever you are coding the front end on. A simple configuration
paramter would tell you how many fields there are.
Now, if you don't need to retain your current table structure, I would recommend switching it to a name/value pairing table.
Essentially orienting your data vertical, in rows, instead of horizontal in columns. One way would be to have 3 columns:
type,seqno,val. Type would be vectore, scalar, etc. Seqno would just be the number you assign and the val would be the val of that
item.
This allows you to easily select all values of a particular type. This also allows you to index everything, so searching on all
vectors greater than x would be very fast. It also automatically adjusts to the contract.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, January 11, 2007 5:00 AM
Subject: Select where the column names are not fully known
We have a logging package that logs data into a table. Part of the data
that is logged are elements of a vector. A logging application
automatically takes out the elemets of the vector and automatically
creates column names based on it's name and the element. This data will
be mixed in with lots of other scalar data, but the elements will be
created next to each other. For example, I may have a table with column
names like this:
datetime
scalar1
scalar2
scalar3
vector_1
vector_2
vector_3
vector_4
scalar4
scalar5
etc etc. I reality there is a lot more data than this. I neet to be able
to select only the items of the vector - like this:
select vector_1,vector_2,vector_4,vector_4 from mytable where
datetime=somedatetime.
So far so good. However the problem is that the vector size can vary
from contract to contract. I don't want to have to change the select
command for each contract we do. The format of the column name is always
itemname_x where x starts at 0.
So I'd like to do something like
select vector_* from mytable where datetime=somedatetime.
but of course this doesn't work.
Anyone any ideas how I can do this?
Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
