|
Rob, I suspect there is no way around this yet - we can have only 1 active
connection at a time, and a JOIN like this would need more than 1.
It'd be nice if IBM would add this capability - especially now that we CAN
specify the remote database name in our 3-part naming.
I've no information suggesting they ARE going to do that - and could not
say so, anyhow!! It does seem an obvious next step. And if someone were to
write a DRDA provider for things like SQL Server, hey, we could do this all
directly to other than IBM i platforms.
Vern
On 2/16/2015 8:49 AM, rob@xxxxxxxxx wrote:
One of the newer functions out is three part naming, for example
select col1, col2, col3
from myLparName.MySchema.MyTable
So this statement is correct:
SELECT a.cmPNY, a.CMPNAM
FROM gdisys.erplxf.rco a
And if I have WRKRDBDIRE all set up I can change it to point to another
lpar like this:
SELECT a.cmPNY, a.CMPNAM
FROM gdi.erplxf.rco a
However, I cannot do this:
SELECT a.cmPNY, a.CMPNAM, b.cmpnam
FROM gdisys.erplxf.rco a
join gdi.erplxf.rco b using (cmpny)
Nor this:
SELECT a.cmPNY, a.CMPNAM, b.cmpnam
FROM gdisys.erplxf.rco a, gdi.erplxf.rco b
where a.cmpny=b.cmpny
These last two abort with:
SQL0512
Message . . . . : Statement references objects in multiple databases.
Cause . . . . . : The statement refers to objects that reside on
multiple
databases. The objects could be either explicitly qualified object
names or
alias names that are defined to reference a different database. This
SQL
statement can only refer to a single database.
A CREATE TABLE AS that selects from a remote database cannot
reference
a
table that has a FIELDPROC defined. It cannot define a materialized
query
table. If the remote database is not on an IBM i server, the INCLUDING
clauses are not allowed.
Recovery . . . : Ensure all objects used in the statement reside at the
same database. Try the request again.
I can do this:
Create table rob.localcopy as (
SELECT a.cmPNY, a.CMPNAM
FROM gdi.erplxf.rco a)
with data
But we're really hoping to get away from copying data all around.
Is there some magic wand I can wave around to make this work?
Rob Berendt
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.