Integration

Error:
UA service error (Transaction cannot include batched SQL (semicolon delimited) statements.)

SQL:

INSERT INTO TEMP_HECM_REFI_TBL select * from F17RDMS.HECM_REFI_TBL where borrower_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5);
INSERT INTO TEMP_HECM_REFI_TBL select * from F17RDMS.HECM_REFI_TBL where coborrower1_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5) AND CASE_NO NOT IN (SELECT CASE_NO FROM TEMP_HECM_REFI_TBL);
INSERT INTO TEMP_HECM_REFI_TBL select * from F17RDMS.HECM_REFI_TBL where coborrower2_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5) AND CASE_NO NOT IN (SELECT CASE_NO FROM TEMP_HECM_REFI_TBL);
INSERT INTO TEMP_HECM_REFI_TBL select * from F17RDMS.HECM_REFI_TBL where coborrower3_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5) AND CASE_NO NOT IN (SELECT CASE_NO FROM TEMP_HECM_REFI_TBL);
INSERT INTO TEMP_HECM_REFI_TBL select * from F17RDMS.HECM_REFI_TBL where coborrower4_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5) AND CASE_NO NOT IN (SELECT CASE_NO FROM TEMP_HECM_REFI_TBL);
SELECT * FROM TEMP_HECM_REFI_TBL;

I originally had a simple select:
select * from hecm_refi_tbl
where
borrower_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5) or
coborrower1_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5) or
coborrower2_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5) or
coborrower3_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5) or
coborrower4_ssn in (#ssn1,#ssn2,#ssn3,#ssn4,#ssn5)

That worked but it was a table scan... took about 30 seconds to return.

I tried a union next but that turned out to be a bust also as the RDMS we use doesn't return column names on unions (I know, right?... sheesh!)

The batch uses a temp table and works real well in a query tool, not so much when I use it in a DB adapter.

I'm thinking I may have to write a view and have the DBA install it but, before I do that I thought I would see if there is a way to quickly fix this (or if someone wit a better grasp on SQL can suggest a better single query.)

Thanks y'all