Tom Deskevich wrote:
I am creating an import file and the person receiving it
wants a null when the field is blank. I am truncating all
blanks. But I send her " ". The help references a null,
but does not seem to apply to what I want.
CPYTOIMPF FROMFILE(LTCFILE) TOSTMF(&FILE) +
MBROPT(*REPLACE) STMFCCSID(*PCASCII) +
RCDDLM(*CR) RMVBLANK(*BOTH)
To export the database NULL value, the datum must be the NULL
value [i.e. the column indicates there is no data for that row]
versus a string of blanks or even the empty string.
An "empty string" is not the "database NULL value" even if
sometimes called a "null string". IMO the RMVBLANK(*BOTH) should
effect an "empty string" versus a single blank. However even that
is probably still insufficient\undesirable for the recipient. Be
sure to clarify if the recipient wants the "null character" instead,
which typically means the character 0x00 [or written x'00']; i.e. to
say that the recipient "wants a null" is not explicit enough IMO,
although I would infer [or dare I say, /assume/] that their desire
is that no delimiters nor any data would appear between the field
delimiters. In my experience that is how a database import facility
[e.g. CPYFRMIMPF] would expect to receive "the null value" from a
text export. If they want the null character, they do not
understand what a true "text" export is all about; i.e. 0x00 in the
stream causes the output have a "binary" requirement.
One option is to create a VIEW over the LTCFILE. Make the VIEW
return NULL for blanks in the field(s) of interest; e.g. using the
CASE. Then when exporting, reference that VIEW as the FROMFILE.
The export facility will not generate any value [no delimiters nor
any data] between the field delimiters for a NULL value. So for
example:
create table ltcfile (l date, t time, c char)
;
insert into ltcfile (c) values
('A'),(' '),('C'),(default),('')
;
create table ltcXfile as
(select a.*,'X' as X from ltcfile a)
with data
;
create view ltcfileVW (l, t, c) as
(select l, t, case when c<>'' then c end
from ltcfile )
;
create view ltcXfileVW (l, t, c, X) as
(select l, t, case when c<>'' then c end, X
from ltcXfile )
;
Although not truly effecting the NULL value as "no data between
field delimiters", another option is to produce the export file
without string delimiters; i.e. do not provide any string delimiter
for the export request, by specifying STRDLM(*NONE) on the
CPYTOIMPF. I do not recall the effect of the "remove blanks"
setting without delimiters, but even if that effects only one versus
several blanks between the field delimiters [e.g. a comma], whatever
import facility used by the recipient may interpret the data as NULL
for a null capable column; i.e. for lack of any standards for
various formats, just test to see if ", ," is acceptable versus [in
my experience is generally expected as] ",," which is the outcome
for the NULL value on export.
Using the above example setup, compare the results of the
following requests to see what happens for each export; note that
the first two columns are the NULL value for all rows:
cpytoimpf fromfile(ltcfile) strdlm(*none)
cpytoimpf fromfile(ltcXfile) strdlm(*none)
cpytoimpf fromfile(ltcfileVW) /* strdlm(choose)*/
cpytoimpf fromfile(ltcXfileVW) /* strdlm(choose) */
Regards, Chuck
midrange.com
