IBM i (AS400) to SQL Server table join syntax

Question

I am using Excel Connection to query customer contracts from DB2 for IBM i (AS400) through SQL Server connection and trying to join a SQL Server table to determine contract expiration date and sales team responsibility.

The AS400 query operates but I continue to receive an error on joining the SQL Server table ACCOUNT.dbo.CUSTOMER but can't find reference to alternate syntax on the join.

[select  *
from openquery(
bpcsrpt_new,'
select s.SCID, s.SVER, s.CONTEXP, a.ACCTNAME, a.SALESTEAM

from AS400table1.contract c, AS400table1.subcontract s, ACCOUNT.dbo.CUSTOMER a

where c.cid=''Active''
and c.cid=s.scid
and c.cver=s.sver
and c.cid=a.acid')]

Answer 1

That's not going to work. When you use openquery, the statement gets sent to the remote machine. Obviously, ACCOUNT.dbo.CUSTOMER is not on the remote IBM i (aka AS400) machine.

You could use 4 part naming in the query directly

select s.SCID, s.SVER, s.CONTEXP, a.ACCTNAME, a.SALESTEAM
from IBMILNKNAM.IBMIDBNAM.IBMILIBNAM.contract c
     , IBMILNKNAM.IBMIDBNAM.IBMILIBNAM.subcontract s
     , ACCOUNT.dbo.CUSTOMER a
where c.cid='Active'
and c.cid=s.scid
and c.cver=s.sver
and c.cid=a.acid

Note however, the SQL Server will pull back the complete contract and subcontract tables to do the join locally.

Openquery is a better option if you're only interested in a few rows of a large table on the IBM i. If I recall correctly, something like so: (not tested)

select  * 
from (select * from Openquery(IBMIKNKNAM, 'select s.SCID, s.SVER, s.CONTEXP 
                            from contract c
                                 join subcontract s 
                                       on c.cid=s.scid
                                       and c.cver=s.sver
                           where c.cid=''Active'')) as rmt
      join ACCOUNT.dbo.CUSTOMER a on a.acid = rmt.cid