I am trying to execute the following but am getting a syntax error. DB2 version I am using is 8.1.
SET SUBSTR(VNDRNOTE1RSLT001,19,9) = '777777777'
WHERE SUBSTR(VNDRNOTE1KEY,1,4) = 'SAGE'
AND SUBSTR(VNDRNOTE1KEY,5,10) = :WS-SEL-VNDR-NBR
AND SUBSTR(VNDRNOTE1RSLT001,19,9) = :WS-SEL-SS-ID
I doubt table design, because many SUBSTRs are used for columns.
It would be better to separate columns, like:
---> VNDRNOTE1KEY_1 CHAR(4) NOT NULL , VNDRNOTE1KEY_VNDR_NBR CHAR(10) NOT NULL , .....
---> VNDRNOTE1RSLT001_1 CHAR(18) , VNDRNOTE1RSLT001_SS_ID CHAR(9) , .....
There may be a possibility to get better performance by modifying conditions...
WHERE SUBSTR(VNDRNOTE1KEY , 1 , 4) = 'SAGE'
AND SUBSTR(VNDRNOTE1KEY , 5 , 10) = :WS-SEL-VNDR-NBR
WHERE VNDRNOTE1KEY LIKE 'SAGE' || :WS-SEL-VNDR-NBR || '%'
The table design is delivered vendor code so it cannot be modified. However, the changes for the SQL look good and I will be testing them today. Thanks so much for the prompt responses. I am very optimistic about your suggestions.