Re: SQLRPGLE insert null

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

On 24-Sep-2015 15:22 -0600, Luis Rodriguez wrote:

You could use a CASE statement for each field you want to test.
Something like this:

INSERT INTO MYLIB.MYFILE(
Field1
, Field2
, Field3
)
VALUES(
(Case :newfield1 when ' ' THEN null else :newfield1 END)
, (Case :newfield2 when ' ' THEN null else :newfield2 END)
, (Case :newfield3 when ' ' THEN null else :newfield3 END)
)
;

To be clear, what was shown coded above are examples of the scalar
CASE-expression; an important distinction, from the CASE-statement.

And for that simple effect of a CASE-expression, the NULLIF scalar
is much simpler and succinct; i.e. code instead for each value:
NULLIF('',:newfield#)

Ugh. I had failed to consult the docs before composing that example. A correction to the above example, a similarly important distinction, is that the *first* argument of the NULLIF scalar should be the desired non-null value, in order to effect the equivalent of the quoted CASE-expressions:
NULLIF(:newfield#,'')

[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzscanullif.htm]

IBM i 7.2->Database->Reference->SQL reference->Built-in functions->Scalar functions->
_NULLIF_
"The NULLIF function returns a null value if the arguments compare equal, otherwise it returns the value of the first argument.

>>-NULLIF--(--expression-1--,--expression-2--)------------><

The arguments must be compatible data types.
..."

[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcaseexp.htm]

IBM i 7.2->Database->Reference->SQL reference->Language
elements->Expressions->
_CASE expression_
<<SNIP>>