Re: Help: creating view with different columns?

A possible table structure may be:

anr datatype data
j1 length 100
j1 material wood

j1    weight     50
j2    length     33

From your exsample below j1 may be a hammer and j2 a nail. Now you can use tools like Crystal Report or MS Access and their ability to transpose this table into the matrix you want. These tools can also output the data to a file with a ; as delimiter. You do have a problem in that the type of data in the data-column above will vary widely. You will have to make this a char column that's large enough for your largest data. If you want to create sums or other calculations on the data you would probably want to use another structure so you could have columns of more sensible types for this.

weberj_at_t-online.de (Juergen Weber) wrote:

:J. Duhl wrote:
:>
:> Juergen Weber wrote:
:> >
:> > 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.
:
:A given article mostly will use several columns.
:
: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.
:
:The customer is a hardware retailer.
:A given article might use several columns, e.g. a
:hammer handle might use 'length','material','weight'
:wheras a nail might only use 'length'
:
:> > 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 have a couple of questions and a suggestion:
:> 1. What kind of data is put into the columns?
:
:Integers and strings, data describing hardware articles for
:carpenters.
:> 2. Does a relational database have to be used?
:The data should be used for database publishing. The program
:used to put the data into xpress (in-between) wants ";" delimeted
:tables. So every system for database storage is possible, that finally
:creates such a table (for db publishing only a part of the articles is
:necessary at a time).
:
:> 3. Can you express the problem generically without
:> expressing that you have to use a tabular database?
:see above
:
:> However, since I don't fully understand your problem, this answer
:> may be way off the mark, a risk I'm willing to take to get a clearer
:> sense of your problem.
:
:I am very grateful for your remarks,
:
:
:Greetings,
:--
:Juergen Weber
:Computer Scientist in the Prepress Business
:email: weberj_at_schwend.de www: http://www.schwend.de

Nils.Myklebust_at_idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company
The Informix FAQ is at http://www.iiug.org Received on Mon Apr 14 1997 - 00:00:00 CEST