Re: SQLRPGLE insert null

On 24-Sep-2015 15:28 -0600, CRPence wrote:

On 24-Sep-2015 15:08 -0600, Justin Taylor wrote:

If a field is blank, I want to write a null when I do an INSERT.
There could be one or more nulls. Is there a good way to do this?
The only way I've found so far is a bunch of different INSERT
statements and IF/ELSE's.

Not sure what is the scenario; DDL and some example data might be
more clear.

But if I understand correctly what was attempted to be expressed in
that text, I think the following might assist; presumes an INSERT
with VALUES clause:

INSERT INTO the_lib/the_table VALUES
( nullif( :F1_value , '' )
, nullif( :F2_value , '' )
)

The above example was revised, to switch the :Fld_HV_value to be the first argument, because the "NULLIF function returns a null value if the arguments compare equal, otherwise it returns the value of the first argument."

The NULLIF scalar will produce a NULL-result if the value of the
host variable named F1_value equates with the empty-string [varlen]
or equates with all blanks for a fixed-length field. Thus the
determination whether the NULL-value is the effect, is pushed into
the database rather than being coded separately\before in the
host-language.

And according to the docs, as I included in my most recent reply [http://archive.midrange.com/rpg400-l/201509/msg00184.html] correcting effectively the same example in another reply to this thread, the *first* argument would be returned when the values do not compare equal. Thus the revision being made, to what I had originally composed\posted in the quoted text above. Birgitta's most recent reply [http://archive.midrange.com/rpg400-l/201509/msg00182.html] showed the proper invocation\form of the the scalar to effect what is desired, but with no mention of my near-identical suggestion having been malformed; what I thought was a somewhat redundant reply, I realized then, was apparently a correction.

From the description in the OP [above], seems unclear whether the
IF\ELSE work is being used to set the NULL indicators, and perhaps
the host-indicator-variables are already being supplied; e.g.:


INSERT INTO the_lib/the_table VALUES
( :F1_value :F1_ind )
, :F2_value :F2_ind )
)