Re: SQL: Joining on three part names.

On 17-Feb-2015 07:10 -0600, rob@xxxxxxxxx wrote:

On 16-Feb-2015 08:49 -0600, rob@xxxxxxxxx wrote:

<<SNIP>>
However, I cannot do this:

SELECT a.cmPNY, a.CMPNAM, b.cmpnam
FROM gdisys.erplxf.rco a
join gdi.erplxf.rco b
using (cmpny)

<<SNIP>>

If I'm going to copy to the local system then I can already do that
with existing three part naming:

Create table rob.localcopy as (
SELECT a.cmPNY, a.CMPNAM
FROM gdi.erplxf.rco a )
with data

No additional product needed going between multiple IBM i's.

<<SNIP>>

Instead of data for rob.localcopy being placed in a permanent object, could the results be scoped to just the one query as a temporary result of a SQL UDTF using the three-part name? Admittedly that would still make reference to multiple remote databases in one statement, but would not be making direct references to the three-part names in the statement. Perhaps [on gdisys] the following enables the query to run without error and with the data copied locally, but that data residing locally just for that query?:

create function "gdi.erplxf.rco" ()
returns table
( cmpny char ...
, cmpnam varchar ...
, ...
)
language sql
return
select
cmpny
, cmpnam
, ...
from gdi.erplxf.rco

SELECT a.cmPNY, a.CMPNAM, b.cmpnam
FROM gdisys.erplxf.rco a
join table( "gdi.erplxf.rco"() ) b
using (cmpny)