AW: SQLRPGLE insert null

What about using the scalar function NULLIF!
Again you need to check each column value separately

Insert into YourSchema.YourTable
Values(NULLIF(:HostVar1, ''), NULLIF(:HostVar2, 0), :HostVar3, :HostVar4,
NULLIF(:HostVar5, '0001-01-01') ...);

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Justin
Taylor
Gesendet: Friday, 25.9 2015 16:30
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: RE: SQLRPGLE insert null

I tried using null indicators before, but it wouldn't compile. Tried it
again and it worked. I obviously had something wrong the first time.

Thanks

-----Original Message-----
From: CRPence [mailto:crpbottle@xxxxxxxxx]
Sent: Thursday, September 24, 2015 4:28 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQLRPGLE insert null

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 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.

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

--
Regards, Chuck


--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.