Re: Table split into separate server and instance
kh teoh asked how to join tables in separate instances, following Michael Krzepkowski's tip. Michael confirmed the database@instance:table syntax. Jonathan Leffler corrected the query by adding a FROM clause with aliases. kh teoh then got error 908 (connection to the database server failed, conerr -25555) on a db4@TEST and db6@TEST join and asked what he did wrong. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Michael Krzepkowski <NOmichaelkSPAM@sqlcanada.com> wrote in message news:<3EEA1186.3020204@sqlcanada.com>... > You can split them (create two tables, then populate them) and you can > join them > in one select - however do not expect the performance to be any good if > these tables > are huge. Hint: any column in Informix can be expresses as > "database@instance:tablename.column" > > HTH > > Michael Is it like this ? SELECT HR@instance1:employee.id, HR@instance1:employee.name, HR@instance1:employee.address, HR@instance1:employee.telno, HR@instance1:employee.dob, HR@instance2:employee.epfno, HR@instance2:employee.password, HR@instance2:employee.salary WHERE HR@instance1:employee.id = HR@instance2:employee.id
kh teoh wrote: >Michael Krzepkowski <NOmichaelkSPAM@sqlcanada.com> wrote in message news:<3EEA1186.3020204@sqlcanada.com>... > > >>You can split them (create two tables, then populate them) and you can >>join them >>in one select - however do not expect the performance to be any good if >>these tables >>are huge. Hint: any column in Informix can be expresses as >>"database@instance:tablename.column" >> >>HTH >> >>Michael >> >> > >Is it like this ? > >SELECT HR@instance1:employee.id, HR@instance1:employee.name, >HR@instance1:employee.address, HR@instance1:employee.telno, >HR@instance1:employee.dob, HR@instance2:employee.epfno, >HR@instance2:employee.password, HR@instance2:employee.salary >WHERE HR@instance1:employee.id = HR@instance2:employee.id > > Yes.
kh teoh wrote:
> Michael Krzepkowski <michaelk@sqlcanada.com> wrote:
>> You can split them (create two tables, then populate them) and
>> you can join them in one select - however do not expect the
>> performance to be any good if these tables are huge. Hint: any
>> column in Informix can be expresses as
>> "database@instance:tablename.column"
>
> Is it like this ?
>
> SELECT HR@instance1:employee.id, HR@instance1:employee.name,
> HR@instance1:employee.address, HR@instance1:employee.telno,
> HR@instance1:employee.dob, HR@instance2:employee.epfno,
> HR@instance2:employee.password, HR@instance2:employee.salary
> WHERE HR@instance1:employee.id = HR@instance2:employee.id
More or less - it depends on how much typing you like to do, and
whether you care about the syntax error :-)
SELECT E1.id, E1.name, E1.address, E1.telno, E1.dob,
E2.epfno, E2.password, E2.salary
FROM HR@instance1:employee AS E1, HR@instance2:employe AS E2
WHERE E1.id = E2.id;
That add's the missing FROM clause (syntax error), and uses table
aliases to make it easier to spot which material comes from which
database.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
I've got error when I do this :-
select a.branch_code, a.user_id, b.branch_code, b.user_id
from db4@TEST:code_pwdaccess a, db6@TEST:code_pwdaccess b# ^
# 908: Attempt to connect to database server (test, conerr=-25555, oserr=0 fail
ed.
#
where a.user_id = b.user_id
What I did wrong here ?