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_at_schwend.de

To: informix-list _at_ 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_at_schwend.de www: http://www.schwend.de