0

How can I use case or something else to handle errors so my query does not crash. For example I would like to select 1 column and 1 "formula" based on that column... something like this:

select column01 ,
       case ('if column01 is number then integer(column01) else 'error'')
from table01

I would basically like the second column to convert column01 into integer, which already works for me fine... but if converting causes an error I would like to see the word 'error'

or at least have the second column say 'OK' when the convertion is possible and 'error' when it is not.

I am using DB2

Robert Pitrone
  • 109
  • 1
  • 3
  • 13
  • You want a `case` expression. Note that its different return types must be compatible. – jarlh Mar 15 '18 at 09:09
  • That `case` doesn't really do anything useful. All `case` subexpression must return the value of the same data type, therefore the value returned by `integer(column01)` is cast right back to `varchar` in order to be compatible with the character literal `'error'`. – mustaccio Mar 15 '18 at 12:37
  • 1
    Possible duplicate of [DB2- How to check if varchar field value has integers](https://stackoverflow.com/questions/10489703/db2-how-to-check-if-varchar-field-value-has-integers) – Stavr00 Mar 15 '18 at 13:57
  • What you're looking for, in the general sense, is an equivalent to Some/None behavior from functional programming. Your best bet is possibly using `null` as the "None" value here, which has the benefit of flowing through lots of other calculations if you're messing with additional columns – Clockwork-Muse Mar 15 '18 at 18:13

1 Answers1

3
select column01,
CASE
  WHEN LENGTH(RTRIM(TRANSLATE(column01, '*', ' 0123456789'))) = 0 
  THEN INTEGER(column01)
  ELSE 'Error'
END as ColumnType
from table01
Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27
  • @RobertPitrone - Note that my version of DB2 (iSeries), at least, appears to be swallowing the word 'Error' and returning something invalid (but not erroring out). – Clockwork-Muse Mar 15 '18 at 18:10