Re: Continually Changing Table Schema
Date: Wed, 30 Jul 2003 15:12:13 +0100
Message-ID: <bg8k75$5cp$1_at_pheidippides.axion.bt.co.uk>
> I'm working on a project to allow users to search a database table. The
> requirement is that users can add an unlimited number of keys (columns) of
> string or integer data type at anytime.
Try this (example is in Oracle):
drop table multiple;
create table multiple (RecordKey integer, FieldID int, Value varchar(16));
insert into multiple values (1, 1, 'rec1field1');
insert into multiple values (1, 2, 'rec1field2');
insert into multiple values (1, 3, 'rec1field3');
insert into multiple values (1, 4, 'rec1field4');
insert into multiple values (2, 1, 'rec2field1');
insert into multiple values (2, 2, 'rec2field2');
insert into multiple values (2, 3, 'rec2field3');
insert into multiple values (2, 4, 'rec2field4');
drop view tableview;
create view tableview as
select t1.recordkey, t1.value as field1, t2.value as field2,
t3.value as field3, t4.value as field4
from multiple t1, multiple t2, multiple t3, multiple t4
where t1.fieldID=1
and t2.fieldID=2 and t3.fieldID=3 and t4.fieldID=4 and t1.recordkey = t2.recordkey and t1.recordkey = t3.recordkey and t1.recordkey = t4.recordkey;
select t.recordkey, field1, field2, field3, field4
from tableview t, multiple m
where t.recordkey = m.recordkey
and value='rec2field3';
- to add a new field. The TABLEVIEW view will not see the new fields. insert into multiple select distinct recordkey, (select max(fieldID) from multiple)+1, 'new value' from multiple