Tweet
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:
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.
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:
Code:
--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);
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.
Comment