Re: SQL: Joining on three part names.

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