Search code examples
sqldb2odbcunionibm-midrange

AS400 Query/Excel ODBC Union Error

I'm having issues getting a query working in Excel for our AS400/DB2 system.

I'm trying to use two different tables with different info, but return an aisle/slot in the same column based upon a specific SKU/Customer ID(Storer). I thought a UNION would work but I get an "SQL0802 - Data conversion or data mapping error" when trying to run the query.

Here's what I have right now:

SELECT ADJTRAN.AJAISL AS AISLE, ADJTRAN.AJSLOT AS SLOT
FROM S216F06V.WDLSDATA.ADJTRAN ADJTRAN
WHERE (AJITEM=8011989 AND AJSTOR=581)
UNION
SELECT ILCATER.ILAISL AS AISLE, ILCATER.ILSLOT AS SLOT
FROM S216F06V.WDLSDATA.ILCATER ILCATER
WHERE (ILITEM=8011989 AND ILSTOR=581)

Any help would be appreciated.

EDIT: AJAISL, AJSLOT, ILAISL and ILSLOT are character fields with a length of 4.

Solution

  • AJITEM and ILITEM are VARCHAR(20). I was comparing it to an INT. Fixed and the code below works with zero issues. My fault for not stating this in the original question and overlooking such a simple mistake.

    SELECT ADJTRAN.AJAISL AS AISLE, ADJTRAN.AJSLOT AS SLOT
FROM S216F06V.WDLSDATA.ADJTRAN ADJTRAN
WHERE (AJITEM='8011989' AND AJSTOR=581)
UNION
SELECT ILCATER.ILAISL AS AISLE, ILCATER.ILSLOT AS SLOT
FROM S216F06V.WDLSDATA.ILCATER ILCATER
WHERE (ILITEM='8011989' AND ILSTOR=581)