Re: views on external databases

Bill Good news! and some Bad news We are running Online 5.XX on an RS/6000 and I was able to successfully create the view accessing the data in other database. I also created a form using the view and it worked fine. It picked up data changes in the orignal database with a hitch. But I was unable to join tables/view from different databases. I could join two local views/tables and could join two remote views/tables but could not get a local and remote table/view to join, just got a no records selected message. Hope it helps. -------------------------------------------------------------------------------- Paul Watje SPAMIS Data Center paulw@spamis.jcdc.eta.gov Largest Informix Shop Voice: (800) 598-5008 in San Marcos, TX FAX: (512) 396-6600 On Mon, 18 Jul 1994, Bill Dixon wrote: } We have Informix SE (v5.?) running on an IBM RS/6000. We have the } need to do something that we think is possible in Informix OnLine, } but is not possible in SE (we tried). Whenever we call Informix to } see if one of their products will do something, we always seem to get } the standard marketing answer, "of course it will, it will do } anything you want it to". (My appologies to non-marketing employees } of Informix :-) Could someone out there with access to OnLine please } confirm this for us? } } On page 11-25 of our SQL Tutorial manual, it says "INFORMIX-OnLine } permits you to base a view on tables and views in external } databases." Here is what we want to do, put as simply as possible: } } One database is called "main". It contains two tables, "customer" & } "state". The customer table has one record per customer. The state } table has one record per state (Alabama, Alaska, etc). } } Then there needs to be a second database, called "branch". This } database will also have a table called "customer", using the same } schema as the main database. However, since the states don't change } (short of a civil war), there is no need to duplicate that data. } Therefore, the branch database needs to contain a view, called } "state", on the state table in the main database. } } The sql statements we are using to try to set up the view on the branch } database are: } } > create view state_tbl ( } > state_code, } > state_name ) } > as select } > m.state_code, } > m.state_name } > from main:state_tbl m } } With SE I receive: } > Error 554: Syntax disallowed in this database server. } } If this works the way we think it should, it should make no } difference which database a set of sql statements is run on, it } should run the same, without modification. } } Although this is not the scenario we are actually interested in, it } should (I hope) convey the essence of what we need to accomplish. If } some kind net.soul out there with access to Informix OnLine could } check this out for us, and let us know for sure whether it works, we } would be much appreciative. Either e-mail or posting would be okay. } } Thank you for your assistance. } -- } Bill Dixon }