Re: In SQl, how do you determine if fielda divided by field b has any remainder?

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Rob

Your formula is a variation on what is used to calculate the modulus, 
according to the documentation -

MOD(x,y) = x - (x/y) * y where x/y is the truncated integer result of 
the division

Your WHEN could be (fielda / fieldb) * fieldb - fielda > 0 or maybe more 
general as int(fielda / fieldb) * fieldb - fielda > 0

This aligns with Birgitta's comment, which allows mod() to work with 
more general scenario of values with decimal places.

Regards
Vern

On 1/15/2020 9:22 AM, Rob Berendt wrote:
> Birgitta,
> Are you sure about that?
> CREATE TABLE QTEMP.MYTABLE2 (FIELDA INT NOT NULL WITH DEFAULT,
> FIELDB INT NOT NULL WITH DEFAULT)
>
> select fielda, fieldb,
> case when (fielda / fieldb) * fieldb <> fielda then 'remainder'
> else  'no remainder' end
> from qtemp.mytable2
>
> FIELDA          FIELDB   CASE expression
>       2               1    no remainder
>      99              13    remainder
>       7               2    remainder
>       7               3    remainder
>       7               4    remainder
>       7               5    remainder
>       7               7    no remainder
>       7               6    remainder
>
>
> Rob Berendt