Let us say we have below COBOL working storage section table and we want to match all the employee numbers from this array to a DB2 table.
01 W-EMP-COUNT PIC S9(04) COMP.
01 W-EMP-LIST.
05 W-EMP-NUM OCCURS 1 TO 50 TIMES DEPENDING ON W-EMP-COUNT
PIC X(05).
01 W-EMP-LIST.
05 W-EMP-NUM OCCURS 1 TO 50 TIMES DEPENDING ON W-EMP-COUNT
PIC X(05).
Below SQL does exactly that
WITH TEMP(IDX, T_EMP_NUM) AS
(SELECT 1, LEFT(:W-EMP-LIST,5)
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT IDX+1, SUBSTR(:W-EMP-LIST,((IDX)*5)+1,5)
FROM TEMP
WHERE IDX < :W-EMP-COUNT
),
SELECT * FROM EMP_TABLE, TEMP
WHERE EMP_NO = EMP_NUM;
(SELECT 1, LEFT(:W-EMP-LIST,5)
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT IDX+1, SUBSTR(:W-EMP-LIST,((IDX)*5)+1,5)
FROM TEMP
WHERE IDX < :W-EMP-COUNT
),
SELECT * FROM EMP_TABLE, TEMP
WHERE EMP_NO = EMP_NUM;