> Hi John,
>
> 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.
Thank you Steffen. That answers my question perfectly. Some small
examples just
like yours would be a valuable addition to the documentation!
> 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.
I come from an Informix background. Informix has a nonstandard extension
to their
SQL : LOAD FROM filename INSERT INTO tabname
and its inverse:
UNLOAD TO filename SELECT ..... FROM tabname ...
These both use the format of ASCII pipe terminated fields, one record
per line.
The last field of each line has a trailing pipe.
Null values are stored in empty fields (two adjacent pipes:||).
My ambition is to find a way to use repmcli DATALOAD to read the
Informix unload files.
So for me, it would be wonderful if SAPDB's default behaviour was to
interpret
empty fields as null.
Topic 2 - loading aberrant DATE fields
Informix supply a toy database (in their meaning of database: collection
of tables
with data) called STORES. This allows you to load data and play with
SQL.
The stores data is UNLOADed in America, where they apparently represent
dates in
MM/DD/YY format. To get these into SAPDB, I have been writing Perl
scripts to
substite the re patterns for MM/DD/YY into (19|20)YYMMDD. Is there a way
to
get repmcli DATALOAD to do this for me? (If so a minimal example would
be
much appreciated!).
If the date load would be possible except for the 2 digit year, would it
be possible
to load dates represented as ASCII MM/DD/YYYY ?
Regards
John O'Gorman
>
> Regards,
> Steffen
> --
> Steffen Schildberg
> SAP DB Team
> SAP Labs Berlin
>
u 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
