HI,
I need to change the data in one field VENDOR_NUMBER where value is 'None' to null.
When I run this,I get updated = 0. why?


DEFINE FILE SWFFEDVENDOR
VENDOR_NUMBER1/A10 = IF VENDOR_NUMBER IS 'None' THEN '' ELSE VENDOR_NUMBER;
END

TABLE FILE SWFFEDVENDOR
PRINT
SHIPPER_NAME
DIVISION
VENDOR_NUMBER1 AS 'VENDOR_NUMBER'
VENDOR_NAME1
VENDOR_NAME2
CITY
STATE
ZIP
WHERE VENDOR_NUMBER IS 'None'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS TEMP1 FORMAT ALPHA
END
-*
MODIFY FILE SWFFEDVENDOR
FIXFORM FROM TEMP1
MATCH SHIPPER_NAME
ON MATCH UPDATE VENDOR_NUMBER
ON NOMATCH REJECT
DATA ON TEMP1
END

This message has been edited. Last edited by: vkrugman,

Are you mixing VENDOR_NUMBER and VENDOR_NAME?

NO SORRY IT'S A TYPO

This message has been edited. Last edited by: vkrugman,

Do you have MISSING=ON in your source master?

There is also another setting called HOLDMISS that will propagate missing values to a hold file.

You can do a keyword search on the IBI site to get additional details.

Maybe the blank Vendor Number field is not ACTIVE in the MODIFY, so I would do it this way:

TABLE FILE SWFFEDVENDOR
PRINT
SHIPPER_NAME
WHERE VENDOR_NUMBER IS 'None'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS TEMP1 FORMAT ALPHA
END
-*
MODIFY FILE SWFFEDVENDOR
FIXFORM FROM TEMP1
MATCH SHIPPER_NAME
ON MATCH COMPUTE VENDOR_NUMBER = '';
ON MATCH UPDATE VENDOR_NUMBER
ON NOMATCH REJECT
DATA ON TEMP1
END

Verify that you actually have records in file TEMP1.

quote:

DEFINE FILE SWFFEDVENDOR
VENDOR_NUMBER1/A10 = IF VENDOR_NUMBER IS 'None' THEN '' ELSE VENDOR_NUMBER;
END

Try this:
DEFINE FILE SWFFEDVENDOR
VENDOR_NUMBER1/A10 MISSING ON= IF VENDOR_NUMBER IS 'None' THEN MISSING ELSE VENDOR_NUMBER;
END

'' is NOT the same as NULL
Also, trying to do this from a HOLD FORMAT ALPHA file is going to be a problem because fixed format alpha is going to have a hard time trying to represent a NULL field. A space is a space.

One other thing to bear in mind that if it is a key field, or part of one (with missing? ) then it would not be updated either. I would have thought that this scenario would have generated an error but the grey cells won't give me access to that part of the memory bank

T