Question on joining tables

Hi, I have a task of creating a view/temp table from two tables joined by a primary key. The problem is I don't know in advance what columns the two tables have and thus I cannot avoid selecting the duplicate columns (the primay key). The statement select * from table1, table2 returns the joined tables correctly because Informix seems to be able to handle the duplicate columns. However the statement select * from table1, table2 into temp table3 gives an error "Column <colname> already exists in table <table3>. Is there a way to get around it without specifiying the columns by name? Thanks. Vikki Sent via Deja.com http://www.deja.com/ Before you buy.

sounds like you need to grab the column names from systables and create a programmatic solution , like 4gl, or esql/c? vleung@syndesis.com wrote: > Hi, > > I have a task of creating a view/temp table from two tables joined by a > primary > key. The problem is I don't know in advance what columns the two tables > have > and thus I cannot avoid selecting the duplicate columns (the primay > key). > The statement > > select * from table1, table2> > returns the joined tables correctly because Informix seems to be able to > handle the duplicate columns. However the statement > > select * from table1, table2 into temp table3> > gives an error "Column <colname> already exists in table <table3>. Is > there a > way to get around it without specifiying the columns by name? Thanks. > > Vikki > > Sent via Deja.com http://www.deja.com/ > Before you buy.

vleung@syndesis.com wrote: > > Hi, > > I have a task of creating a view/temp table from two tables joined by a > primary > key. The problem is I don't know in advance what columns the two tables > have > and thus I cannot avoid selecting the duplicate columns (the primay > key). > The statement > > select * from table1, table2 I assume there is a proper WHERE clause with join conditions omitted here. > returns the joined tables correctly because Informix seems to be able to > handle the duplicate columns. However the statement > > select * from table1, table2 into temp table3 and here. > gives an error "Column <colname> already exists in table <table3>. Is > there a > way to get around it without specifiying the columns by name? Thanks. Are you using 4GL or ESQL/C? Then you can PREPARE the first SELECT and then DESCRIBE it, using either a DESCRIPTOR AREA or Informix's sqlda data structures and walk the resulting structure or descriptor to determine the column names then you can create a SELECT statement with specific column names selected and giving aliases to the duplicated colnames in the temp table. You can do something similar using CLI/ODBC also. If you want a pure SQL solution, no can do. Using SQL with a scripting language you can take Edward's advice and query systables and syscolumns to build a query. Art S. Kagel