Month: April 2008

Updated: File Layout SQL

My post yesterday was a little wrong. Here is a better SQL to help with the sequence of the fields in the layout:

[sourcecode language=’sql’]SELECT * FROM (

SELECT A.FLDFIELDNAME, A.FLDSTART,

(SELECT COUNT(*) FROM PSFLDFIELDDEFN

WHERE FLDDEFNNAME = A.FLDDEFNNAME

AND FLDSTART < A.FLDSTART) + 1 CORRECT_SEQ, A.FLDSEQNO ACTUAL_SEQ FROM PSFLDFIELDDEFN A WHERE A.FLDDEFNNAME = '

) B

WHERE B.CORRECT_SEQ <> B.ACTUAL_SEQ

ORDER BY B.FLDSTART[/sourcecode]
I had trouble rearranging the fields though. You can drag and drop the fields, but when you reopen the file layout, you find that it didn’t actually adjust the field sequence (FLDSEQNO). So, the fields are back in the order they were in. This is true for tools 8.46; I didn’t test 8.49.The only way I could find to rearrange the fields was to delete the field from the layout. Then, click on the field that you wanted it after and re-insert it.

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.

[sourcecode language=’sql’]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 = ‘

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;

[/sourcecode]
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.
[sourcecode language=’sql’]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 = ‘
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;

[/sourcecode]