Re: Multiple Databases in SQL (Online 5.07)

On Thu, 9 Oct 1997, Tony Marasco wrote: > I read in the Informix book (one paragraph) that it is possible to access > multiple tables in different databases. Is this possible in the same SQL > statement? If so, how do you code it? > > I tried something like: SELECT somecol FROM db1.table1, db2.table2 WHERE > etc. SELECT T1.SomeCol, T2.OtherCol FROM somedbs@server:'owner'.tablename T1, otherdbs@othersvr:'user'.tothertable T2 WHERE T1.PkCol = T2.FkCol ... You can drop bits and pieces of the table name notation more or less as required. Specifically, you don't have to specify the server if the database is on the same OnLine instance as your current database, and you don't have to specify the owner name unless you're using a MODE ANSI database and don't own the tables. I use table aliases (the T1, T2 in the example) because I decline to rewrite the long names everywhere throughout the query -- they aren't mandatory, and you could write: SELECT somedbs@server:'owner'.tablename.SomeCol, otherdbs@othersvr:'user'.tothertable.OtherCol FROM somedbs@server:'owner'.tablename, otherdbs@othersvr:'user'.tothertable WHERE somedbs@server:'owner'.tablename.PkCol = otherdbs@othersvr:'user'.tothertable.FkCol ... However, IMNSHO, this is far less readable than the previous edition of the query. This is documented in the manual. In the 7.2 Informix Guide to SQL: Reference, under SELECT statement, it refers you to p1-768 for details of how to form a table name. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>