Executing Defines In Joined Files

The report request below is to obtain a count of current active/on-leave positions by collective bargaining id and FLSA status. It contains an error in the define. Can you spot it?

EX JACPS

define file ps add

flsa/a10 = decode ps:flsa (n 'non-exempt' e 'exempt' o 'other');

end

table file ac

count ac:POSITION

by ac:CBID

by flsa

end

If you said the DEFINE FILE command is referring to the wrong file, you are correct. The command should be: DEFINE FILE AC ADD. If you are wondering why it should refer to the AC file when a field in the PS file is being manipulated, the reason is because of the pre-programmed join. When a join is executed, the defined fields in the cross-referenced file (PS) are executed via the host file (AC). Therefore, all file references in the DEFINE FILE command must refer to the host file.

If you're not sure which is the host file and which is the cross-referenced file, refer to the join name. All pre-programmed joins have the same naming convention: (Join)(host file)(cross-referenced file). For example, the join between the AC and PS file is: JACPS. Some joins have more than one cross-referenced file, but the first file named is always the host file.