Re: A "Load from" question.

The Wilcoxons wrote: > Jonathan Leffler wrote in message <6up72t$g1b$1@news.xmission.com>... > >On Mon, 28 Sep 1998, Suhas Tembe wrote: > >> I have a temp table "temp_table" with say 3 columns as ; > >> > >> col1 char(2); > >> col2 char(2); > >> col3 char(2); > >> > >> I also have a delimited (|) flat file with 3 columns & I have > >> to load data from the flat file into this "temp_table". [...] > >> ab||cd --- line 1 [...] > >> > >> load from "flat_file.txt" insert into temp_table;> >>[...] > >> As you can see, a "null" is inserted into the column(s) if there > >> is no data. (Note : There is no space between the pipes in the > >> flat file). My question is, what would I have to do if I wanted > >> "spaces" & not "nulls" to be inserted into the table. [...] > > > >Put spaces in the load file instead of leaving the fields empty: > > > >sed -e 's/^|/ |' \\ > > -e 's/||/| |/g' \\ > > -e 's/||/| |/g' \\ > > -e 's/|$/| /' flat_file.txt > newfile > >mv newfile flat_file.txt > > > ---snip--- > > Another technique is if the fields are defined to default to > spaces instead of permitting nulls. That does require a change of > the database change, but it does permit you on a field by field > basis to fill in different values in different fields. Did you test this hypothesis? I think you'd be disappointed, because DEFAULT values only apply when the column is not mentioned in the INSERT statement. Now, every implementation of LOAD I've seen generates an INSERT statement analogous to: INSERT INTO temp_table(col1, col2, col3) VALUES (?, ?, ?); The alternative doesn't list any of the columns by name, but that is functionally the same as listing all the columns. As far as DEFAULT values are concerned, all the columns are specified, so DEFAULT never gets a chance to act -- so your load will either fail with errors because the underlying columns do not accept nulls or will insert nulls into the database. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>