I'm trying to use MODIFY to add values to a DB table. When I run the fex, I get the below error:

1
0 NUMBER OF RECORDS IN TABLE= 4287 LINES= 4287
1
0 NUMBER OF RECORDS IN TABLE= 10342 LINES= 10342
1
0 NUMBER OF RECORDS IN TABLE= 4287 LINES= 18
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
(FOC1500) : Microsoft SQL Server Native Client 11.0: [] Null accessors are not
(FOC1500) : supported by this provider.
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
0 TRANSACTIONS: TOTAL = 1 ACCEPTED= 1 REJECTED= 0
SEGMENTS: INPUT = 0 UPDATED = 0 DELETED = 0


I checked our master file and there is only one field that does not allow NULLS, however that field will auto populate by increments of 1. That field is the RECID. I'm not sure what else is causing this issue. Any help is appreciated. Below is my code:

SET ASNAMES = ON
SET EMPTYREPORT = ON
SET BYDISPLAY = ON
SET NODATA = .
SET SQUEEZE = ON


-DEFAULT &CURYEAR = '2018';
-DEFAULT &BEGMON = '3';
-DEFAULT &FIELDTYPE = 'FieldNo';
-DEFAULT &SUMMARYBY = 'CONTRACT';
-DEFAULT &BEGDATE = 03/01/2018;
-DEFAULT &ENDDATE = 03/31/2018;
-DEFAULT &BUSINESS_SEGMENT = 'FOC_NONE';
-DEFAULT &SUB_BUSINESS_SEGMENT = 'FOC_NONE';
-DEFAULT &DIVISION = 'FOC_NONE';
-DEFAULT &SUB_DIVISION = 'FOC_NONE';
-DEFAULT &COST_CENTER_GROUP = 'FOC_NONE';
-DEFAULT &CONTRACT = 'FOC_NONE';
-DEFAULT &ACCOUNT = 'FOC_NONE';
-DEFAULT &WFFMT = 'HTML'

-SET &FIELDFILTER = IF &FIELDTYPE = 'FieldNo' THEN '((ACTIVITY_SEQ EQ MISSING ) OR ( ACTIVITY_SEQ EQ 0))' ELSE IF &FIELDTYPE = 'FieldYes' THEN 'FOC_NONE' ELSE 'SUB_PROJECT_ID NE MISSING';
-SET &TEMPBEGDATE = IF &BEGDATE NE 'FOC_NONE' THEN EDIT(&BEGDATE, '99$99$9999') ELSE 'FOC_NONE';
-SET &TEMPENDDATE = IF &ENDDATE NE 'FOC_NONE' THEN EDIT(&ENDDATE, '99$99$9999') ELSE 'FOC_NONE';
-SET &PYBEGDATE = IF &BEGDATE NE 'FOC_NONE' THEN DATECVT(DATEADD(DATECVT (&TEMPBEGDATE,'I8MDYY', 'YYMD'),'Y', -1 ), 'YYMD','I8YYMD') ELSE 'FOC_NONE';
-SET &PYENDDATE = IF &ENDDATE NE 'FOC_NONE' THEN DATECVT(DATEADD(DATECVT (&TEMPENDDATE,'I8MDYY', 'YYMD'),'Y', -1 ), 'YYMD','I8YYMD') ELSE 'FOC_NONE';
-SET &SHOWCOLUMN = IF &SUMMARYBY EQ 'CONTRACT' THEN 'SITE_DESC' ELSE &SUMMARYBY;
-*-SET &PART_TYPE = IF &ACCOUNT EQ '41500' THEN 'PARTS' ELSE IF &ACCOUNT EQ '41503' THEN 'ENG' ELSE IF &ACCOUNT EQ '41504' THEN 'WHEEL' ELSE 'FOC_NONE';


TABLE FILE IFS_MPCCOM_ACCOUNTING
SUM
IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.DEBIT_CREDIT_AMOUNT
BY IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.ACCT_ID
BY IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.CODE_B
BY IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.ACCOUNT_NO
BY IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.ACCOUNTING_YEAR
BY IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.ACCOUNTING_PERIOD
WHERE IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.ACCOUNT_NO IN ('41500','41503','41504');
-*WHERE IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.ACCOUNT_NO IN ('41503');
WHERE IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.CODE_B LIKE 'M%' OR 'D%' OR 'G%';
WHERE IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.ACCOUNTING_YEAR EQ &CURYEAR;
WHERE IFS_MPCCOM_ACCOUNTING.MPCCOM_ACCOUNTING.ACCOUNTING_PERIOD EQ &BEGMON;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS ACTGDATA FORMAT XFOCUS INDEX ACCT_ID
-*ON TABLE PCHOLD FORMAT HTML
END

DEFINE FILE IFS_INVENTORY_TRANSACTIONS
ACTUAL_QTY/D12.2=IF IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.DIRECTION EQ '-' THEN IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.QUANTITY * -1 ELSE IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.QUANTITY;
ACTUAL_COST/D12.2=ACTUAL_QTY * IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.COST;
ACT_COST_PART/D12.2 = ACTUAL_COST/ACTUAL_QTY;
CYQTY/D12.2=IF ((DATE_APPLIED GE DT(&BEGDATE)) AND (DATE_APPLIED LE DT(&ENDDATE))) THEN ACTUAL_QTY ELSE 0;
CYAMT/P33.2=IF ((DATE_APPLIED GE DT(&BEGDATE)) AND (DATE_APPLIED LE DT(&ENDDATE))) THEN ACTUAL_COST ELSE 0;
CYCSTPART/D12.2=IF ((DATE_APPLIED GE DT(&BEGDATE)) AND (DATE_APPLIED LE DT(&ENDDATE))) THEN ACTUAL_COST ELSE 0;
-*ACCOUNT/A20V = IF ACCOUNTING_GROUP EQ 'PARTS' THEN '41500' ELSE IF ACCOUNTING_GROUP EQ 'ENG' THEN '41503' ELSE IF ACCOUNTING_GROUP EQ 'WHEEL' THEN '41504' ELSE 'FOC_NONE';
END

TABLE FILE IFS_INVENTORY_TRANSACTIONS
SUM
IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.CYQTY
IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.CYAMT
-* COMPUTE CYCSTPT/P33.2=IF CYQTY NE '0' THEN CYAMT/CYQTY ELSE 0.00;
-* COMPUTE COST_PART/P33.2 =IF (DIVISION EQ 'Rail Grinders' AND CYCSTPT GE '15000') THEN CYCSTPT ELSE IF (DIVISION IN ('Ditchers','Rail Train') AND CYCSTPT GE '3000') THEN CYCSTPT ELSE IF (DIVISION EQ 'Track Inspection' AND CYCSTPT GE '1000') THEN CYCSTPT ELSE IF (DIVISION IN ('Railvacs', 'Specialty Grinders', 'Shoulder Ballast Cleaners', 'Undercutters') AND CYCSTPT GE '5000') THEN CYCSTPT ELSE 0.00;
BY TRANSACTION_ID
BY LOWEST SITE_DESC
BY LOWEST CONTRACT
BY ACCOUNTING_GROUP
BY DATE_APPLIED
BY LOWEST IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.PART_NO
BY LOWEST IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.PART_DESC
WHERE (( IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.DATE_APPLIED GE DT(&BEGDATE ) ) AND ( IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.DATE_APPLIED LE DT(&ENDDATE ) )) OR (( IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.DATE_APPLIED GE DT(&PYBEGDATE ) ) AND ( IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.DATE_APPLIED LE DT(&PYENDDATE ) ));
WHERE IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.BUSINESS_SEGMENT EQ 'Contract Services';
WHERE (IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.ACCOUNTING_GROUP NE 'STONES');
WHERE IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.TRANSACTION_CODE NE 'COUNT-OUT' OR 'COUNT-IN';
WHERE IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.COST GT 1 OR IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.COST LT -1;
WHERE IFS_INVENTORY_TRANSACTIONS.INVENTORY_TRANSACTION_HIST2.ACTIVITY_SEQ EQ MISSING OR 0;
-*WHERE TOTAL COST_PART NE '0.00';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS INVTDATA FORMAT XFOCUS INDEX TRANSACTION_ID
-*ON TABLE PCHOLD FORMAT HTML
END


JOIN
INNER ACTGDATA.SEG01.ACCT_ID IN ACTGDATA TO UNIQUE
INVTDATA.SEG01.TRANSACTION_ID IN INVTDATA TAG J0 AS J0
END

JOIN
LEFT_OUTER ACTGDATA.SEG01.CODE_B IN ACTGDATA TO UNIQUE
DW_CODE_B_V2.DW_CODE_B_V2.CODE_B IN dw_code_b_v2 TAG J1 AS J1
END

JOIN
LEFT_OUTER ACTGDATA.SEG01.ACCOUNT_NO IN ACTGDATA TO UNIQUE
DW_ACCOUNT.ACCOUNT_EXPORT.ACCOUNT IN dw_account TAG J2
AS J2
END


DEFINE FILE ACTGDATA
YEAR/I4 = &CURYEAR;
PERIOD/I4 = &BEGMON;
STATUS/A50 = 'Active';
QUANT/A25=FTOA(CYQTY, '(D20.0)', QUANT);
VAR_NOTE/A300V = 'Part ' | PART_NO||';' | ' Qty ' | QUANT|'; ' | PART_DESC;
TDAY/MDYY = &MDYY;
ENTERED_BY/A50V = 'Automated';
ENTERED_DATE/HYYMDs = TDAY;
-*MODIFIED_BY/A50 = 'NULL';
-*MODIFIED_DATE/HYYMDs = ;
END

TABLE FILE ACTGDATA
SUM
J0.SEG01.CYQTY
J0.SEG01.CYAMT
COMPUTE CYCSTPT/P33.2=IF CYQTY NE '0' THEN CYAMT/CYQTY ELSE 0.00; NOPRINT
COMPUTE COST_PART/P33.2 =IF (DIVISION EQ 'Rail Grinders' AND CYCSTPT GE '15000') THEN CYCSTPT ELSE IF (DIVISION IN ('Ditchers','Rail Train') AND CYCSTPT GE '3000') THEN CYCSTPT ELSE IF (DIVISION EQ 'Track Inspection' AND CYCSTPT GE '1000') THEN CYCSTPT ELSE IF (DIVISION IN ('Railvacs', 'Specialty Grinders', 'Shoulder Ballast Cleaners', 'Undercutters') AND CYCSTPT GE '5000') THEN CYCSTPT ELSE 0.00;
COMPUTE COST_PER_PART/P33.2 = IF CYQTY LT '0' THEN COST_PART * -1 ELSE COST_PART;
-*BY LOWEST TRANSACTION_ID
BY LOWEST PART_NO
BY LOWEST PART_DESC
BY LOWEST YEAR
BY LOWEST PERIOD
BY LOWEST ACTGDATA.SEG01.ACCOUNT_NO
BY DW_ACCOUNT.ACCOUNT_EXPORT.DESCRIPTION
BY DW_ACCOUNT.ACCOUNT_EXPORT.ACCNT_GROUP
BY DW_ACCOUNT.ACCOUNT_EXPORT.ACCNT_GROUP_DESC
BY ACTGDATA.SEG01.CODE_B
BY J0.SEG01.SITE_DESC
BY DW_CODE_B_V2.DW_CODE_B_V2.DIVISION
BY DW_CODE_B_V2.DW_CODE_B_V2.SUB_DIVISION
BY DW_CODE_B_V2.DW_CODE_B_V2.COST_CENTER_GROUP
BY STATUS
-*BY VAR_NOTE
WHERE YEAR EQ &CURYEAR;
WHERE INVTDATA.SEG01.ACCOUNTING_GROUP NE 'STONES';
WHERE TOTAL COST_PART NE '0.00';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS TEMPDATA FORMAT XFOCUS
END

DEFINE FILE TEMPDATA
QUANT/A25=FTOA(CYQTY, '(D20.0)', QUANT);
VAR_NOTE/A300V = 'Part ' | PART_NO||';' | ' Qty ' | QUANT|'; ' | PART_DESC;
ENTERED_BY/A50V = 'Automated';
TDAY/MDYY = &MDYY;
ENTERED_DATE/HYYMDs = TDAY;
MODIFIED_BY/A20V = ;
MODIFIED_DATE/HYYMDs = ;
RECID/I11 = ;
END

TABLE FILE TEMPDATA
PRINT
-*PART_NO NOPRINT
-*RECID AS 'RecID'
YEAR AS 'Year'
PERIOD AS 'MonthNbr'
ACCOUNT_NO AS 'Account'
DESCRIPTION AS 'Account_Desc'
ACCNT_GROUP AS 'Acct_Group_No'
ACCNT_GROUP_DESC AS 'Acct_Group_Desc'
CODE_B AS 'Code_B'
SITE_DESC AS 'Code_B_Desc'
DIVISION AS 'Code_B_Div'
SUB_DIVISION AS 'Code_B_Sub_Div'
COST_CENTER_GROUP AS 'Code_B_Group'
CYAMT AS 'Variance_Amt'
STATUS AS 'Status'
VAR_NOTE AS 'Notes'
ENTERED_BY AS 'EnteredBy'
ENTERED_DATE AS 'EnteredDate'
MODIFIED_BY AS 'ModifiedBy'
MODIFIED_DATE AS 'ModifiedDate'
BY RECID
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD
-*ON TABLE PCHOLD FORMAT HTML
END

MODIFY FILE VARIANCE_NOTES
SQL SET LOADONLY
FIXFORM FROM HOLD
MATCH RECID
ON MATCH REJECT
ON NOMATCH INCLUDE
DATA ON HOLD
END


Thanks

This message has been edited. Last edited by: Jen Moser,

Are there any null RECIDs in the HOLD file?
Try turning on the SQL Trace to see what the modify translates into and see if you can do the same thing in SQL Server outside of WF.

Also, edit variance_notes.mas and see if you have a FIELDTYPE=R for any of the columns you're trying to update.

DEFINE FILE TEMPDATA
RECID/I11 = ;

This is your key index column, and, you're setting it to NULL. Only 1 record out of your 18 would ever get loaded, so, really can't tell what you are trying to do.
With that, you say that value is auto-incremented - by what? Another process?

Even if you set it to ZERO, only 1 record would load. Just don't know what you are trying to do...

I turned on the SQL Trace, and this is what I received:

SET TRACEOFF=ALL
SET TRACEUSER=CLIENT
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEON = SQLTRANS
SET TRACESTAMP = OFF
-RUN

AGGREGATION DONE ...
SELECT
T1."ACCOUNTING_ID",
T1."CODENO_B",
T1."ACCOUNT_NO",
T1."ACCOUNTING_YEAR",
T1."ACCOUNTING_PERIOD",
SUM(T1."DEBIT_CREDIT_AMOUNT")
FROM
LORA1APP.MPCCOM_ACCOUNTING T1
WHERE
(T1."ACCOUNTING_PERIOD" = 3) AND
(T1."ACCOUNTING_YEAR" = 2018) AND
((T1."CODENO_B" LIKE 'M%') OR (T1."CODENO_B" LIKE 'D%') OR
(T1."CODENO_B" LIKE 'G%')) AND
(T1."ACCOUNT_NO" IN('41500', '41503', '41504'))
GROUP BY
T1."ACCOUNTING_ID",
T1."CODENO_B",
T1."ACCOUNT_NO",
T1."ACCOUNTING_YEAR",
T1."ACCOUNTING_PERIOD"
ORDER BY
T1."ACCOUNTING_ID",
T1."CODENO_B",
T1."ACCOUNT_NO",
T1."ACCOUNTING_YEAR",
T1."ACCOUNTING_PERIOD";
1
0 NUMBER OF RECORDS IN TABLE= 4287 LINES= 4287
AGGREGATION DONE ...
SELECT
T1."TRANSACTION_ID",
LORA1APP.CODE_B_API.Get_Description('CONSOL200', T1."CONTRACT"),
T1."CONTRACT",
LORA1APP.INVENTORY_PART_API.Get_Accounting_Group(T1."CONTRACT",
T1."PART_NO"),
T1."DATE_APPLIED",
T1."PART_NO",
LORA1APP.INVENTORY_PART_API.Get_Description(T1."CONTRACT",
T1."PART_NO"),
SUM((CASE WHEN ((T1."DATE_APPLIED" >= TO_DATE('01-03-2018',
'DD-MM-YYYY')) AND (T1."DATE_APPLIED" <= TO_DATE['31-03-2018',
'DD-MM-YYYY'))) THEN (CASE (T1."DIRECTION") WHEN '-' THEN
(T1."QUANTITY" * -1) ELSE T1."QUANTITY" END) ELSE 0 END)),
SUM((CASE WHEN ((T1."DATE_APPLIED" >= TO_DATE('01-03-2018',
'DD-MM-YYYY')) AND (T1."DATE_APPLIED" <= TO_DATE['31-03-2018',
'DD-MM-YYYY'))) THEN ((CASE (T1."DIRECTION") WHEN '-' THEN
(T1."QUANTITY" * -1) ELSE T1."QUANTITY" END) * T1."COST") ELSE
0 END))
FROM
LORA1APP.INVENTORY_TRANSACTION_HIST2 T1
WHERE
((T1."ACTIVITY_SEQ" IS NULL) OR (T1."ACTIVITY_SEQ" = 0)) AND
((T1."COST" > 1) OR (T1."COST" < -1)) AND
(T1."TRANSACTION_CODE" NOT IN('COUNT-OUT', 'COUNT-IN')) AND
(LORA1APP.INVENTORY_PART_API.Get_Accounting_Group(T1."CONTRACT",
T1."PART_NO") <> 'STONES') AND
(LORA1APP.ACCOUNTING_STRUCTURE_ITEM_API.Get_Description('999',
'RPT COST CENTER', 'Node',
LORA1APP.ACCOUNTING_STRUCTURE_ITEM_API.Get_Item_Above('999',
'RPT COST CENTER', 'Node',
LORA1APP.ACCOUNTING_STRUCTURE_ITEM_API.Get_Item_Above('999',
'RPT COST CENTER', 'Node',
LORA1APP.ACCOUNTING_STRUCTURE_ITEM_API.Get_Item_Above('999',
'RPT COST CENTER', 'Node',
LORA1APP.ACCOUNTING_STRUCTURE_ITEM_API.Get_Item_Above('999',
'RPT COST CENTER', 'Node',
LORA1APP.ACCOUNTING_STRUCTURE_ITEM_API.Get_Item_Above('999',
'RPT COST CENTER', 'Node',
LORA1APP.ACCOUNTING_STRUCTURE_ITEM_API.Get_Item_Above('999',
'RPT COST CENTER', 'CodePartValue', T1."CONTRACT"))))))) =
'Contract Services') AND
((T1."DATE_APPLIED" BETWEEN TO_DATE('01-03-2017','DD-MM-YYYY')
AND TO_DATE('31-03-2017','DD-MM-YYYY')) OR (T1."DATE_APPLIED"
BETWEEN TO_DATE('01-03-2018','DD-MM-YYYY') AND
TO_DATE('31-03-2018','DD-MM-YYYY')))
GROUP BY
T1."TRANSACTION_ID",
LORA1APP.CODE_B_API.Get_Description('CONSOL200', T1."CONTRACT"),
T1."CONTRACT",
LORA1APP.INVENTORY_PART_API.Get_Accounting_Group(T1."CONTRACT",
T1."PART_NO"),
T1."DATE_APPLIED",
T1."PART_NO",
LORA1APP.INVENTORY_PART_API.Get_Description(T1."CONTRACT",
T1."PART_NO")
ORDER BY
T1."TRANSACTION_ID",
LORA1APP.CODE_B_API.Get_Description('CONSOL200', T1."CONTRACT"),
T1."CONTRACT",
LORA1APP.INVENTORY_PART_API.Get_Accounting_Group(T1."CONTRACT",
T1."PART_NO"),
T1."DATE_APPLIED",
T1."PART_NO",
LORA1APP.INVENTORY_PART_API.Get_Description(T1."CONTRACT",
T1."PART_NO");
1
0 NUMBER OF RECORDS IN TABLE= 10346 LINES= 10346
FOC2510 - FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
FOC2514 - NON-CONTIGUOUS SQL SUBTREE
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED
SELECT
T3."DIVISION",
T3."SUB_DIVISION",
T3."COST_CENTER_GROUP"
FROM
IfsProdStaging.dbo.CODE_B_V3 T3
WHERE
(T3."CODE_B" = ?);
SELECT
T4."DESCRIPTION",
T4."ACCNT_GROUP",
T4."ACCNT_GROUP_DESC"
FROM
IfsProdStaging.dbo.ACCOUNT_EXPORT T4
WHERE
(T4."ACCOUNT" = ?);
1
0 NUMBER OF RECORDS IN TABLE= 4287 LINES= 18
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
SELECT T1."RecID",T1."Year",T1."MonthNbr",T1."Account",
T1."Account_Desc",T1."Acct_Group_No",T1."Acct_Group_Desc",
T1."Code_B",T1."Code_B_Desc",T1."Code_B_Div",
T1."Code_B_Sub_Div",T1."Code_B_Group",T1."Variance_Amt",
T1."Status",T1."Notes",T1."EnteredBy",T1."EnteredDate",
T1."ModifiedBy",T1."ModifiedDate" FROM
IFSWarehouse.dbo.VarianceNotes T1 WHERE (T1."RecID" = ?);
INSERT INTO IFSWarehouse.dbo.VarianceNotes() VALUES ();
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
(FOC1500) : Microsoft SQL Server Native Client 11.0: [] Null accessors are not
(FOC1500) : supported by this provider.
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
0 TRANSACTIONS: TOTAL = 1 ACCEPTED= 1 REJECTED= 0
SEGMENTS: INPUT = 0 UPDATED = 0 DELETED = 0



I went in to my master file and every column is set to FIELDTYPE=R.

Tom, I'll try to explain what I'm trying to do. I'm trying to write a report that will automatically update our warehouse. We have specific requirements that cause a variance, and if those requirements hold true, a note needs to be made. For example, if one of our machines bought a part for over $15,000, we would need to create a note. If I were to manually update the database, I wouldn't have access to fill in the RECID field as they automatically creates a number based off the last field in that table. For example, if the last RECID number is 10, when I add a new line, it automatically adds a RECID of 11. So I'm not sure how to code that....

Jen,
1. If this is the initial load, DEFINE RECID/I11= RECID + 1;
2. If you are now adding a row or rows:

 
   In the step before TEMPDATA, in ACTGDATA step:
      AFTER      COMPUTE COST_PER_PART/P33.2 = IF CYQTY LT '0' THEN COST_PART * -1 ELSE COST_PART;
        ADD:     COMPUTE CNTR/I9 = CNTR + 1;
 

then,

  
TABLE FILE VARIANCE_NOTES
SUM
    MAX.RECID
 ON TABLE HOLD AS RECID FORMAT ALPHA
END
-RUN
-READFILE RECID

Now you have the LAST RECID in &RECID.
Now, your DEFINE is: RECID/I11 = IF CNTR EQ 1 THEN &RECID + 1 ELSE LAST RECID + 1;
If this doesn't work, this will work: RECID/I11 = &RECID + CNTR; this might be more efficient...

Hope this helps, this is 1 way - maybe someone else will also chime in with another suggestion...

This message has been edited. Last edited by: Tom Flynn,

quote:

every column is set to FIELDTYPE=R

Wouldn’t that mean every column you’re trying to update is READ ONLY?

Tom, I tried your way and that didn't work.

I changed my master file, and removed the FIELDTYPE=R for all columns except RECID and that got me closer. Now I received the below error. How does this read to know which field has the invalid character?

Thank you both so much for helping!


1
0 NUMBER OF RECORDS IN TABLE= 4355 LINES= 4355
1
0 NUMBER OF RECORDS IN TABLE= 10386 LINES= 10386
1
0 NUMBER OF RECORDS IN TABLE= 4355 LINES= 18
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1773) FASTLOAD OPERATION FAILED FOR SEGMENT : VARIANCENOTES
0 TRANSACTIONS: TOTAL = 18 ACCEPTED= 18 REJECTED= 0
SEGMENTS: INPUT = 0 UPDATED = 0 DELETED = 0

Maybe try not using FASTLOAD...

Francis, I removed the FASTLOAD and am still getting the below error. I just don't know where I'd look to find which field has the missing character.

1
0 NUMBER OF RECORDS IN TABLE= 4355 LINES= 4355
1
0 NUMBER OF RECORDS IN TABLE= 10404 LINES= 10404
1
0 NUMBER OF RECORDS IN TABLE= 4355 LINES= 18
1
0 NUMBER OF RECORDS IN TABLE= 12972 LINES= 12972
0
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
(FOC1500) : Microsoft SQL Server Native Client 11.0: [22018] Invalid charac
ter
(FOC1500) : value for cast specification
(FOC1740) EXECUTE ERROR : VARIANCENOTES
0 TRANSACTIONS: TOTAL = 18 ACCEPTED= 18 REJECTED= 0
SEGMENTS: INPUT = 0 UPDATED = 0 DELETED = 0

This is the only piece of code that is of interest to us:

MODIFY FILE VARIANCE_NOTES
SQL SET LOADONLY
FIXFORM FROM HOLD
MATCH RECID
ON MATCH REJECT
ON NOMATCH INCLUDE
DATA ON HOLD
END

It appears to generate the following SQL:

INSERT INTO IFSWarehouse.dbo.VarianceNotes() VALUES ();

Is this correct - you're trying to add a row where RECID is automatically incremented by the DB engine? You are not trying to insert data into any other columns?

You could try writing test SQL "pasthru" code:

SET SQLENGINE = SQLMSS
SQL SET SERVER IFSWarehouse /* not sure if this is needed, or if the server name is correct */
SQL
INSERT INTO IFSWarehouse.dbo.VarianceNotes() VALUES ();
END

If you get the same error, then it's a database table issue...

Take a look at this case. I still think FIELDTYPE=R might be a good lead.

https://techsupport.informatio...ttrack/82622026.html

After I removed the FIELDTYPE=R in all columns except RECID in my mas, and removed the date columns in my fex, this now works! Thank you so much for all your help!