How to filter the decimal place in SQL for IBM DB2 database running in IBM-I (as400)

Question

I have a file (or table) suppose Table1 which consists of a numeric field Num1, the values of the field is like 1001.001, 1001.000, 1001.002 and so on . I want to write a SQL query which filters value from this field where the decimal position is not 000. The SQL will select records 1001.001, 1001.002 but will filter out 1001.000.

Can some one suggest how to do this , this query needs to run on IBM DB2 which runs in a IBM AS400 (IBM - I) system.

Answer 1

You can subtract integer part from the number and that test result is 0, if the integer part can fit in an int or bigint :

with table1 (num1) as (
  values
  1001.001,
  1001.000,
  1001.002
)
select * from table1 where num1 - bigint(num1) = 0

NUM1
1001.000

fiddle

Answer 2

the round function also works. Compare the number rounded to zero decimal places to itself.

with table1 (num1) as (
  values
  1001.001,
  1001.000,
  1001.002
)
select  num1, round(num1,0) rounded
from  table1
where  round(num1,0) <> num1 ;

Answer 3

Another option is the mod() function. Modulus 1 will return decimal places.

with table1 (num1) as (
  values
  1001.001,
  1001.000,
  1001.002
)
select * from table1 where mod(num1,1) <> 0