sponsored links



No announcement yet.

SQL - ignore [SQL0404] Value for column or variable x too long ?

  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL - ignore [SQL0404] Value for column or variable x too long ?

    If you have a table that is char(10), and you try and insert a value that is 11 or more characters long, you get error SQLState 22001, code -404, [SQL0404] Value for column or variable x too long

    Is there a way to make the SQL ignore this error and silently truncate instead?

    I know that cast() will silently truncate, and I could also test and convert with case, trimr, length and substr:
    --myCol is char(10), longValue is char(11)
    insert into myTable (myCol)
    values(cast(longValue as char(10)));
    insert into myTable (myCol)
    values(case when length(trimr(longValue)) > 10 then substr(longValue,1,10) else longValue end);
    But scalar transforms on every applicable column on every row will incur performance overhead that I would prefer to avoid, and it means the column lengths are hard-coded into the program.
    So I was hoping there was a way of instructing SQL to do this automatically

    The reasoning for this is I want to store some data returned from the joblog_info() table function. But I have seen that IBM increased the lengths of one of the returned columns in 7.3 compared to the 7.2 version, and I want to protect against the program breaking if they do it again in the future. I'd much rather it truncated instead.