SQL: Joining on three part names

1. SQL: Joining on three part names. (rob@xxxxxxxxx)

I believe there is a solution - it is called MS SQL Server

----------------------------------------------------------------------

message: 1
date: Mon, 16 Feb 2015 09:49:24 -0500
from: rob@xxxxxxxxx
subject: SQL: Joining on three part names.

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
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com