Unltimately, my goal here is to join two databases together by the ENCNTR.ALIAS and ICU_LIST.VST_EXT_ID fields. Here are the master file definitions of the two fields:

FIELDNAME=VST_EXT_ID, ALIAS=vst_ext_id, USAGE=A20, ACTUAL=A20, $
FIELDNAME=ALIAS, ALIAS=ALIAS, USAGE=A200V, ACTUAL=A200V, $
(Although the fields are longer than 7 chars, for my purposes it is a 7 digit number stored as an alphanumeric field.)

I am first creating a hold file on the first database with the following:
DEFINE FILE ICU_LIST
FIN_NUM/A9 = ''''||SUBSTR(20,VST_EXT_ID,1,7,7,'A7')||'''';
END
TABLE FILE ICU_LIST
PRINT
FIN_NUM
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS FIN_LIST FORMAT ALPHA
END

The records on this file look as I would expect when I table file it, ie, '1234567' '2345678' etc.

I'm then querying the second database with this file:
TABLE FILE ENCNTR_ALIAS
PRINT ENCNTR_ID
WHERE ENCNTR_ALIAS.ALIAS IN FILE FIN_LIST;
ON TABLE
END

I would expect this to parse to something like "where alias in ('1234567','2345678')" - which will work when I run this manually- but instead the trace shows the following and nothing is returned:
13.20.19 AE SELECT T1."ENCNTR_ID",T1."ALIAS" FROM V500.ENCNTR_ALIAS T1
13.20.19 AE WHERE (T1."ALIAS" IN('1796197
13.20.19 AE
13.20.19 AE
13.20.19 AE ', '1809173
13.20.19 AE
13.20.19 AE
13.20.19 AE ', '1849962
13.20.19 AE
13.20.19 AE
13.20.19 AE '

Where are all these extra spaces coming from?! Is there a better way of doing this?
Thanks in advance for any advice, my head hurts from banging it up against this wall all day...

This message has been edited. Last edited by: jjoyce,

Since that field is a Define..it must be adding extra spaces. You might need to use the TRIM function and get it down to what you need...just a thought

? FF FIN_LIST

SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN
TABLE FILE BASEL_TIME_D
SUM
COMPUTE PERIOD_TYPE2/A5 = SUBSTR(10, PERIOD_TYPE, 1, 5, 5, 'A5');
BY PERIOD_TYPE NOPRINT
WHERE PERIOD_TYPE IN ('MONTH', 'DAY');
ON TABLE HOLD AS H001
END
?FF H001
-RUN
TABLE FILE BASEL_TIME_D
SUM
DAY_OF_WEEK
BY PERIOD_TYPE
BY DAY_OF_WEEK NOPRINT
WHERE PERIOD_TYPE IN FILE H001;
WHERE READLIMIT EQ 1000;
END

FILENAME=  H001
PERIOD_TYPE2  E01           A5

AGGREGATION DONE ...
SELECT T1."PERIOD_TYPE",T1."DAY_OF_WEEK",
MAX(T1."DAY_OF_WEEK") FROM BASEL.TIME_D T1 WHERE
(T1."PERIOD_TYPE" IN('DAY', 'MONTH')) GROUP BY T1."PERIOD_TYPE",
T1."DAY_OF_WEEK" ORDER BY T1."PERIOD_TYPE",T1."DAY_OF_WEEK"
FETCH FIRST 1000 ROWS ONLY FOR FETCH ONLY;

FIELDNAME=ALIAS, ALIAS=ALIAS, USAGE=A200, ACTUAL=A200, $

SQL DB2 SET VARCHAR OFF

ENGINE [SQLMSS] SET VARCHAR {ON|OFF}