SQL for Maintaining File Layouts

Here are a couple of SQL statements that might help you maintain file layouts.

This SQL shows gaps in a flat file. It assumes that the next field should start at the previous field’s start + the previous field’s length. Make sure to substitute the file layout name.

SELECT A.FLDFIELDNAME, A.FLDSTART, A.FLDLENGTH, A.FLDSTART + A.FLDLENGTH SHOULD_START_POS,

       B.FLDFIELDNAME NEXT_FIELD, B.FLDSTART ACTUAL_START_POS

FROM PSFLDFIELDDEFN A, PSFLDFIELDDEFN B

WHERE A.FLDDEFNNAME = '<file Layout Name>'

AND A.FLDDEFNNAME = B.FLDDEFNNAME

AND B.FLDSTART = (SELECT MIN(FLDSTART) FROM PSFLDFIELDDEFN

                  WHERE FLDDEFNNAME = A.FLDDEFNNAME

                  AND FLDSTART > A.FLDSTART)

AND A.FLDSTART + A.FLDLENGTH <> B.FLDSTART

ORDER BY FLDSTART;

This SQL shows if the fields are out of order. If the order in the file layout designer is different from the order based on the field start position, the layout will be a little confusing and hard to maintain. This should help you correct the order. To re-order the fields in App Designer, drag the field on top of the field that you want it to come after.

SELECT A.FLDFIELDNAME, A.FLDSTART, A.FLDLENGTH, A.FLDSEQNO, A.FLDSEQNO + 1 SEQ_SHOULD_BE,

       B.FLDFIELDNAME NEXT_FIELD, B.FLDSTART NEXT_START_POS, B.FLDSEQNO ACTUAL_SEQ,

       C.FLDFIELDNAME ACTUAL_NEXT_FIELD

FROM PSFLDFIELDDEFN A, PSFLDFIELDDEFN B, PSFLDFIELDDEFN C

WHERE A.FLDDEFNNAME = '<file Layout Name>'
AND A.FLDDEFNNAME = B.FLDDEFNNAME

AND A.FLDDEFNNAME = C.FLDDEFNNAME

AND B.FLDSTART = (SELECT MIN(FLDSTART) FROM PSFLDFIELDDEFN

                  WHERE FLDDEFNNAME = A.FLDDEFNNAME

                  AND FLDSTART > A.FLDSTART)

AND C.FLDSEQNO = A.FLDSEQNO + 1

AND A.FLDSEQNO + 1 <> B.FLDSEQNO

ORDER BY FLDSTART;