Search code examples
sqlnulldb2zos

DB2 SQLCODE = -420, ERROR when comparing smallint fields with nulls

I am trying to compare two smallint fields to look for differences. One table is a pending change table that is read by a batch job at night that then updates the other table along with doing other activities. The batch job has some issues and I'm researching the problems. I want to find those values that are different and show what the pending table's value is when that occurs.

The specific column(s) (CO_FT_FREQ_DAY) that are giving me trouble have nulls in the smallint field and I think that is the cause of my issues. (or it could be a pending add so there is no matching value to compare to...) As you can see I've tried to address the issue but it's still not working.
I tried ifnull before this, but got the same error. I was hoping that this would resolve the issue.

SELECT  T342.clientID
       ,T342.TS_340
       ,case when (case when t342.CO_FT_FREQ_DAY is null then 0 else
                        t342.CO_FT_FREQ_DAY end) <>
                    (case when t340.CO_FT_FREQ_DAY is null then 0 else
                         t340.CO_FT_FREQ_DAY end)
             then T342.CO_FT_FREQ_DAY
             else 0
             end as CO_FT_FREQ_DAY
FROM database.PendingChangeTable     T342
left outer join database.CurrentTable T340
on T340.ClientID = T342.ClientID
and T340.TS_PK = T342.TS_340
WHERE t342.clientID in (clientID list);

DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A STRING ARGUMENT WAS NOT
ACCEPTABLE TO THE DECFLOAT FUNCTION
DSNT418I SQLSTATE = 22018 SQLSTATE RETURN CODE

Solution

  • UGH Found the problem. Whoever designed the table switched midstream and defined these new columns as char fields and I missed that. So I needed to set the value to '0' instead of 0 in my case statement.

           ,case when (case when t342.CO_FT_FREQ_DAY is null then '0' else
                    t342.CO_FT_FREQ_DAY end) <>
                (case when t340.CO_FT_FREQ_DAY is null then '0' else
                     t340.CO_FT_FREQ_DAY end)
         then T342.CO_FT_FREQ_DAY
         else '0'
         end as CO_FT_FREQ_DAY
   ,case when ifnull(t342.CO_HT_FREQ_DAY,'0') <> 
                ifnull(t342.CO_HT_FREQ_DAY,'0')
         then ifnull(T340.CO_HT_FREQ_DAY,'0')
         else '0'
         end as CO_HT_FREQ_DAY

    So the lesson for me today is ALWAYS check your datatypes when you get this error!