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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: