December 03, 2007, 03:25 PM
<GGOFAnalyst>Error Format Mismatch
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
December 03, 2007, 03:39 PM
GinnyJakesquote:
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.
December 03, 2007, 03:51 PM
<GGOFAnalyst>No that didn't fix the problem. Because then the more doesn't work because the fields don't match.
December 03, 2007, 04:11 PM
Francis MarianiJust 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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 03, 2007, 04:12 PM
<GGOFAnalyst>Actually I used define instead of compute and it fixed the problem.
December 03, 2007, 04:20 PM
Francis MarianiAre 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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 04, 2007, 05:30 AM
GamPUsing 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.
December 04, 2007, 09:37 AM
<GGOFAnalyst>Yes I've used SQL Tracing and everything seems to be fine.
December 04, 2007, 12:39 PM
Francis MarianiGood for you
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server