Re: SQLRPGLE insert null

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#)

[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcaseexp.htm]
BM i 7.2->Database->Reference->SQL reference->Language elements->Expressions
_CASE expression_
"CASE expressions allow an expression to be selected based on the evaluation of one or more conditions.

.-ELSE NULL---------------.
>>-CASE-+-searched-when-clause-+-+-------------------------+-END-><
'-simple-when-clause---' '-ELSE--result-expression-'

...

There are two scalar functions, NULLIF and COALESCE, that are specialized to handle a subset of the functionality provided by CASE. The following table shows the equivalent expressions using CASE or these functions.

Table 1. Equivalent CASE Expressions
+===============================================================+
|CASE Expression | Equivalent Expression |
+--------------------------------------+------------------------+
|CASE WHEN e1=e2 THEN NULL ELSE e1 END | NULLIF(e1,e2) |
+--------------------------------------+------------------------+
|CASE WHEN e1 IS NOT NULL THEN e1 | |
| ELSE e2 | COALESCE(e1,e2) |
|END | |
+--------------------------------------+------------------------+
|CASE WHEN e1 IS NOT NULL THEN e1 | |
| ELSE COALESCE(e2,...,eN) | COALESCE(e1,e2,...,eN) |
|END | |
+===============================================================+

..."

Also FWiW, the chosen form of the CASE-expression also can be simplified [i.e. more succinct than what was shown] by using the searched-when-clause form vs the simple-when-clause form, by taking advantage of the implicit ELSE NULL clause:

CASE WHEN :newfield#<>'' THEN :newfield# END