Hi John, John O'Gorman [mailto:[EMAIL PROTECTED]] wrote: > > Previous mails imply say that empty fields only of type CHAR will be > loaded using the repmcli LOAD commmand > Is there any way to load empty fields of other type as NULL. > If not what is the SAPDB way of loading nulls from a load file? > May be I wasn't clear enough in previous mails: if loading empty values into char columns the Loader will insert empty strings if sqlmode is INTERNAL (that's Loaders default) and no NULL value spec is given for that field in the data file. Here is an example:
CREATE TABLE EMTPYTEST (COL1 CHAR(20), COL2 DATE, COL3 INT) // DATALOAD TABLE EMPTYTEST COL1 1 COL2 2 COL3 3 INFILE '<file name>' DELIMITER '' with a data file having rows like this: ,20030217,9 text,,12 Feeding this command file to repmcli the Loader would insert an empty string into COL1 when processing the first row. Processing the 2nd row the Loader would fail and return the following message: 'Data must be compatible with column type and length'. Ok, now we're going to insert NULL values if an empty string is found in the data file for COL2. Here is the example (using the same tab def and data file entries as above): DATALOAD TABLE EMPTYTEST COL1 1 COL2 2 DEFAULT NULL COL3 3 INFILE '<file name>' DELIMITER '' NULL '' This would still force the Loader to insert empty strings into COL1 (no NULL desc for that column). But processing the 2nd row the Loader finds an empty string for COL2 which corresponds to the NULL value specification in the DATALOAD command - the Loader inserts a NULL here. The DEFAULT NULL forces the Loader to use the global (global here stands for: is valid for this DATALOAD command) NULL spec for COL2. You could also define some special NULL value spec for every column which then overwrites the global spec as in: DATALOAD TABLE EMPTYTEST COL1 1 COL2 2 NULL IF POS 2 = ' ' COL3 3 INFILE '<file name>' DELIMITER '' NULL '' To have the Loader insert NULL value for COL1 too you need to specify the NULL value spec for that column, too: DATALOAD TABLE EMPTYTEST COL1 1 DEFAULT NULL COL2 2 DEFAULT NULL COL3 3 INFILE '<file name>' DELIMITER '' NULL '' Now NULLs are inserted into COL1 with the first data row and into COL2 with the 2nd data row. If this lets some more questions arise let us know. It would be interesting to know if there is a need for treating empty values in general as NULL values or if the solution given above is satisfying. Regards, Steffen -- Steffen Schildberg SAP DB Team SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
