I keep getting the error FORMAT MISMATCH FOR THE FIELD: EXPENSEREGION

I've used substring to change the format (length) of the region field to match with the other region field. But I still get this error, I'm not sure what the problem is.

Here's my code:

TABLE FILE EXPENSEBASE
PRINT
COMPUTE CONTACTID/A38 = ' ';
COMPUTE CONTACTNAME/A160 = ' ';
PAYEEID
COMPUTE FULLNAME/A160 = ' ';
COMPUTE POSITION/A100 = ' ';
EXPENSEDATE
COSTCENTER
EXPENSESTATUS
TOTALAMOUNT
COMPUTE BUDGET/D15.2M = &BUDGET;
COMPUTE VARIANCE/D15.2M = BUDGET - TOTALAMOUNT;
EXPENSEREGION AS 'REGION'
EXPENSETYPE
BY EXPENSEID
WHERE EXPENSEREGION NE 'GGOF';
WHERE EXPENSESTATUS EQ 'SUBMITTED';
WHERE EXPENSETYPE EQ 'NON_CONTACT';
ON TABLE HOLD AS NONCONTACT
END
-RUN

JOIN EXPENSEID IN EXPENSEBASE TO ALL EXPENSEID IN EXPENSECONTACT AS J1
JOIN CONTACTID IN EXPENSEBASE TO CONTACTID IN CRMCONTACT AS J2
JOIN PAYEEID IN EXPENSEBASE TO SYSTEMUSERID IN CRMSYSTEMUSER AS J3
-RUN

TABLE FILE EXPENSEBASE
PRINT
CONTACTID
CONTACTNAME
PAYEEID
FULLNAME
POSITION
EXPENSEDATE
COSTCENTER
EXPENSESTATUS
TOTALAMOUNT
COMPUTE BUDGET/D15.2M = &BUDGET;
COMPUTE VARIANCE/D15.2M = BUDGET - TOTALAMOUNT;
COMPUTE REGION/A50 = SUBSTR(50, CF_REGIONNAME, 1, 50, 50, 'A50');
EXPENSETYPE
BY EXPENSEID
WHERE POSITION IN ('RSM', 'ISR', 'SC');
WHERE TOTAL REGION NE 'GGOF';
WHERE EXPENSETYPE EQ 'CONTACT';
WHERE EXPENSESTATUS EQ 'SUBMITTED';
ON TABLE HOLD AS CONTACT
END
-RUN

TABLE FILE CONTACT
PRINT *
ON TABLE HOLD AS ALLCONTACTS
MORE
FILE NONCONTACT
END
-RUN

TABLE FILE ALLCONTACTS
PRINT *
END
-EXIT

quote:

EXPENSEREGION AS 'REGION'
EXPENSETYPE

Try moving this code above the COMPUTE. I think it thinks EXPENSEREGION is a compute and it doesn't have a format. Since you are hold the data, location of fields isn't important.

See if that works.

No that didn't fix the problem. Because then the more doesn't work because the fields don't match.

Just before the last TABLE FILE, add the following to see what fields are in the two HOLD files that you will be merging with the MORE command:

?FF CONTACT
?FF NONCONTACT
-EXIT

The number of fields has to be exactly the same and the formats most likely have to match as well.

Also, do you have SET HOLDLIST=PRINTONLY at the top of your fex? If you don't, computes like COMPUTE REGION/A50 = SUBSTR(50, CF_REGIONNAME, 1, 50, 50, 'A50'); that reference a field that is in the input but not in the output will actually be put in the ouput file (eg. CF_REGIONNAME). PRINTONLY will not put CF_REGIONNAME in the output file.

Actually I used define instead of compute and it fixed the problem.

Are you using SQL tracing to ensure that your code does not generate inefficient SQL?

Doing some kinds of data manipulation in a DEFINE may not be passed to SQL.

Using DEFINE iso COMPUTE seems to indicate that Francis was right with his remark. This means that SET HOLDLIST=PRINTONLY also would have resulted in a correct request. And it also circumvents any possible risks involving sql that does not get passed to the dbms.

Yes I've used SQL Tracing and everything seems to be fine.

Good for you