Invalid argument for function integer IBM DB2

Question

I need to filter out rows in table where numer_lini column has number in it and it is between 100 and 999, below code works just fine when i comment out line where i cast marsnr to integer. However when i try to use it i get error: Invalid character found in a character string argument of the function "INTEGER". when looking at the list seems like replace and translate filters only numbers just fine and select only contains legit numbers (list of unique values is not long so its easy to scan by eye). So why does it fail to cast something? I also tried using integer(marsnr), but it produces the same error. I need casting because i need numeric range, otherwise i get results like 7,80 and so on. As I mentioned Im using IBM DB2 database.

select numer_lini, war_trasy, id_prz1, id_prz2
from alaska.trasa
where numer_lini in (
    select marsnr
    from (
        select
        distinct numer_lini marsnr
        from alaska.trasa
        where case
        when replace(translate(numer_lini, '0','123456789','0'),'0','') = ''
        then numer_lini
        else 'no'
        end <> 'no'
    )
    where cast(marsnr as integer) between 100 and 999
)
fetch first 300 rows only

I'm not familiar with db2, but 7,80 is not an integer. I can imagine you need to specify how you want to convert what are really decimal values like that. — HoneyBadger
no no i mean those are two example numbers i get, it's two integers 7 and 80. — Haruki

Paul Vernon Paul Vernon · Accepted Answer · 2018-06-19T09:39:36

If you look at the optimized SQL from the Db2 explain, you will see that Db2 has collapsed your code into a single select.

SELECT DISTINCT Q2.NUMER_LINI AS "NUMER_LINI",
   Q2.WAR_TRASY AS "WAR_TRASY",
   Q2.ID_PRZ1 AS "ID_PRZ1",
   Q2.ID_PRZ2 AS "ID_PRZ2",
   Q1.NUMER_LINI 
FROM ALASKA.TRASA AS Q1,
   ALASKA.TRASA AS Q2 
WHERE (Q2.NUMER_LINI = Q1.NUMER_LINI) 
AND (100 <= INTEGER(Q1.NUMER_LINI)) 
AND (INTEGER(Q1.NUMER_LINI) <= 999) 
AND (CASE WHEN (REPLACE(TRANSLATE(Q1.NUMER_LINI,
   '0',
   '123456789',
   '0'),
   '0',
   '') = '') THEN Q1.NUMER_LINI 
ELSE 'no' END <> 'no')

Use a CASE to force Db2 to do the "is integer" check first. Also, you don't check for the empty string.

E.g. with this table and data

‪create‬‎ ‪TABLE‬‎ ‪alaska‬‎.‪trasa‬‎ ‪‬‎(‪numer_lini‬‎ ‪VARCHAR‬‎(‪10‬‎)‪‬‎,‪‬‎ ‪war_trasy‬‎ ‪INT‬‎ ‪‬‎,‪‬‎ ‪id_prz1‬‎ ‪INT‬‎,‪‬‎ ‪id_prz2‬‎ ‪INT‬‎)‪;
insert into alaska.trasa values ('',1,1,1),('99',1,1,1),('500',1,1,1),('3000',1,1,1),('00300',1,1,1),('AXS',1,1,1);

This SQL works

select numer_lini, war_trasy, id_prz1, id_prz2
from alaska.trasa
where case when translate(numer_lini, '','0123456789') = ''
            and numer_lini <> ''
           then integer(numer_lini) else 0 end 
     between 100 and 999

Although that does fail if there is an embedded space in the input. E.g. '30 0'. To cater for that, a regular expressing is probably preferred. E.g.

select numer_lini, war_trasy, id_prz1, id_prz2
from alaska.trasa
where case when regexp_like(numer_lini, '^\s*[+-]?\s*((\d+\.?\d*)|(\d*\.?\d+))\s*$'))
           then integer(numer_lini) else 0 end 
     between 100 and 999

Invalid argument for function integer IBM DB2

1 Answers