× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.