Union on AS400 table and SQL Server Table

[donniea21]

Is it possible to do a union on a table from as400 and one from sql server?

I have the following:
preparedStatement = as400.prepareStatement("select distinct claimnbr as CLAIM_NUMBER, claimname AS CLAIMANT_NAME, claimsts as CLAIM_STATUS, t1.srcsystem as SOURCE_SYSTEM, polnpo as POLICY_NUMBER, acctnum, nam1na as POLICYHOLDER_NAME from oplib.mlcrtcxp t1, oplib.plepfafp t2 where trim(polnum)=trim(t1.polhdrnbr) and t1.unassigned > current date and t1.srcsystem='FRD' and acctnum = ?"

preparedStatement.setString(1, polHdrNbr);

and

preparedStatement2 = sqlserver.prepareStatement("select claimnbr, claimantname, status, srcsystem, polnumber, polhdrnbr, polhdrname from pcclaims where rtrim(ltrim(polhdrnbr)) = ?"

preparedStatement2.setString(1, polHdrNbr);

The problem is that the same record could exist in both tables. If this is the case, i only want to show the record from the sqlserver table.

 

[pipk]

couldn't you create a temporary table from the as400 table on the sql server database using the create table command. then you can do the direct comparison on the latter and use union.

not sure if you could run a comparison across two database connections like this without some intermediary step - although it would be great if someone could demonstrate it.

 

[donniea21]

Thanks pipk...thats what i think im going to have to do..Was hoping i could find a little less resource intensive method.