Re: Help: creating view with different columns?

Reformat the table as follows: article number, column_number, data The article number is the same as you have it, the column_number specifies which column contains data, and the data is the data ( may not be needed if X is your only data). The table will no longer be sparsely populated. As to your view, you may want to create a temp table to show the data the way you need it from this table. ( I don't have your original description of the problem, so excuse me if this solution leaves out some detail I'm not aware of). Please respond to weberj@schwend.de To: informix-list @ rmy.emory.edu cc: (bcc: Kate Tomchik/IS/SSC/THD) Subject: Help: creating view with different columns? Maybe somebody has hints for the following DB problem? There is a database of quite different articles. The customer wants us to put everything into one single table. All articles have the article number column in common, but mostly columns are only used for some articles. So the resulting matrix is very sparsely populated. anr ... col_i ... col_k ... col_n anr_j1 X .... anr_jn X .... anr_k1 X .... anr_kn X Together there are over 300 columns. Your solution would work but I would have to specify a query with almost 300 NULL= fields. Also it seems there is no dbms that supports so many columns in one table. Furthermore it seems that empty table fields take space, too, so it is a great waste of storage. So the idea is to create several tables each containing data of similar articles. These tables should be put together by a view to simulate a table that contains everything. The tables should be *updateable* via the view. Ideally I?d like to have a view like CREATE VIEW V AS SELECT * FROM T1 UNION SELECT * FROM T2 UNION.... UNION SELECT * FROM Tn If I change an article via the view the DBMS should know in which single table the article is because the article numbers are unique. I think what I want is impossible, but maybe there is a solution out there? Thanks for your hints. -- Juergen Weber Computer Scientist in the Prepress Business email: weberj@schwend.de www: http://www.schwend.de