I am writing an SQL INSERT using a subselect. The subselect is only populating specific columns in the table I am inserting into. The other columns in the table that I am not populating will either be blanks for character fields, or zero for numeric.
Some of the numeric fields where I am initializing to zero have decimal places, while others do not.
In order to get the INSERT statement to return SQLSTATE of zeros, I needed to add the CAST command to populate the blanks or zeros in the fields I am not pulling in. However, when I execute the prepared statement, I get a SQLSTATE value of 22018. I have done everything I can think of for the CAST. The only thing I have not tried is to include the decimal point for the numeric fields. Is CAST that particular? I would think that if I used:
cast(0 as dec(15,2)),
it would be fine. Do I need to replace that with something like:
cast(0.00 as dec(15,2)), or
cast(0000000000000.00 as dec(15,2))?
We are on IBMi V7R1.