RPG Embedded SQL cast argument on insert statement

[64waves]

Good morning, I have a simple Add/Chg/Delete pgm and I use sql to insert, update and delete.

This original one works, but when I just populate the sku, I get 'Character in CAST argument not valid'. When all fields have a value it inserts fine.

Here is the first version of the insert statement and then my attempt to handle it:

Original:

If @Error = *Blank;
exec sql insert into PrtSku values(
pSku,pDesc,PDim,pwghta,pwghtb,pwghtc);


Retry version:

exec sql insert into PrtSku values(pSku,
case when pDesc is null then ' ' else PDesc end,
case when pDim is null then ' ' else pDim end,
case when pwghta is null then ' ' else pwghta end,
case when pwghtb is null then ' ' else pwghtb end,
case when pwghtc is null then ' ' else pwghtc end);




any help would be appreciated. Thanks.

[TedHolt]

Re: RPG Embedded SQL cast argument on insert statement

 If @Error = *Blank;                                      
   exec sql insert into PrtSku values(                    
           pSku,pDesc,PDim,pwghta,pwghtb,pwghtc);

Is this RPG? What are these values you're inserting? I assume they're not host variables, since they're not preceded by colons. Can you give us a little more information?

[ChrisL]

Re: RPG Embedded SQL cast argument on insert statement

this won't make any difference to whether your code works, but I much prefer using IFNULL() as I find it easier to read..

exec sql insert into PrtSku values(pSku, ifnull(pDesc, ' '), ifnull(pDim, ' '), ifnull(pwghta, ' '), ifnull(pwghtb, ' '), ifnull(pwghtc, ' '));

[64waves]

Re: RPG Embedded SQL cast argument on insert statement

Good morning,

I forgot how to post things so the colons would not be mistaken for smileys. This is RPG, I took the colons out so it wouldn't look stupid. The last 3 fields are 8 0 going into a char field. I needed to do some type of convert that would work in this type of statement. Sorry for the confusion.

[Rocky]

Re: RPG Embedded SQL cast argument on insert statement

If I'm understanding you correctly - pDesc, pDim, pwghta, pwghtb and pwghtc are RPG variables that you're inserting into the table.

The first question is - since you're testing against null - are these variables null capable? If so - don't you have to specify the corresponding null indicators? I don't know as I rarely use null capable variables but I'm thinking you do.