DBSQLIFS command error - Length or start position is out of range for the string operation

From Support

If you have a question or seek clarification, please call Technical Support.

Problem:

Customer is getting the following error in the job log when running the DBSQLIFS command to run a query and summarize data and place into an output file.

Error text from job log:

Length or start position is out of range for the string operation. Error RNX0100 at statement 00001250 in program CPYTODEL1.

CPYERR: Errors occurred while copying file to output file /AS400FILE.TXT.

This error occurs because the AS/400 SQL processor creates a summary field with 31 digits and 2 decimals.

Solution:

A 31,2 sized field is not handled correctly by the DataExport/400 software. A 31,2 field is normally not required for normal numeric calculations because the number is much larger than is usually practical. Shrinking the field size to a smaller, more realistic value will resolve the problem.

Listed below is an example for shortening the field sizes when running a query.

Example of summarizing the BALDUE field from the QCUSTCDT file in library QIWS and creating a summary field with a size of 15,2 :

DBSQLIFS SQL('select decimal(sum(baldue),15,2) from qiws/qcustcdt') IFSFILE('/AS400FILE.TXT')