Subject: Re: In SQl, how do you determine if fielda divided by field b has any remainder? From: Vernon Hamberg Date: Wed, 15 Jan 2020 10:57:19 -0600 List-archive: List-help: List-id: Midrange Systems Technical Discussion List-post: List-subscribe: , List-unsubscribe: ,

Hi Birgitta

The one thing you didn't mention in your example - and what Rob's didn't include - was in the calculation IBM uses for MOD - that the y/x value is truncated to its integer value, even for non-integer values of x and y - just a clarification!

And when both values are known to be integers, it doesn't matter, since what you explain here comes into play.

Regards
Vern

On 1/15/2020 10:50 AM, Birgitta Hauser wrote:
If you calculate 2 integers, the result will be an integer again. This is
the rule and has nothing to do with SQL.

For examples: 100 * 95 / 100 will return 95,
but 100 * (95 / 100) will return 0, i.e. 95 / 100 = 0,95 but because we
devide 2 integers, the result will be an integer again and because we did
not round anything, 0,95 is truncated and 0 * with whatever is still 0.

If we devide through an Decimal (even with 0 decimal positions) the result
is as expected (95):
100 * ( 95 / Dec(100, 11, 0))

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Mittwoch, 15. Januar 2020 17:28
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: In SQl, how do you determine if fielda divided by field b has
any remainder?

But basically the source being integers doesn't cause any issues, right?

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta Hauser
Sent: Wednesday, January 15, 2020 11:26 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: In SQl, how do you determine if fielda divided by field b has
any remainder?

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.

Just do the "case" caluclation within a separate column and decide.

Select x.*,
(FieldA / FieldB) * FieldB "Case Calculation"
Case When (FieldA / FieldB) * FieldB <> FieldA Then 'Remainder' Else
'Equal' End "Remainder"
from mytable x;

Result:
2 1 2 Equal
99 13 91 Remainder
7 2 6 Remainder
7 3 6 Remainder
7 4 4 Remainder
7 5 5 Remainder
7 6 6 Remainder
7 7 7 Equal

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to." (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Mittwoch, 15. Januar 2020 16:23
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: In SQl, how do you determine if fielda divided by field b has
any remainder?

Birgitta,
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
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta Hauser
Sent: Wednesday, January 15, 2020 10:15 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: In SQl, how do you determine if fielda divided by field b has
any remainder?

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.

case when (fielda / fieldb) * fieldb <> fielda
... but if you do this calculation, make sure that FieldA and FieldB are not
defined as integer (at least one of them).
Otherwise you'll never get a remainder.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to." (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Mittwoch, 15. Januar 2020 15:30
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: In SQl, how do you determine if fielda divided by field b has
any remainder?

select fielda, fieldb,
case when (fielda / fieldb) * fieldb <> fielda then 'remainder'
else 'no remainder' end
from qtemp.mytable

FIELDA FIELDB CASE expression
1.00001 1.00001 no remainder
99.99999 2.12345 remainder
2.00000 1.00000 no remainder

Field* defined as 15,5

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan
Shore via MIDRANGE-L
Sent: Wednesday, January 15, 2020 9:08 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: In SQl, how do you determine if fielda divided by field b has any
remainder?

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.

Hi everyone
We are on V7r3
Within SQL - how do you determine if fielda divided by fieldb has any
remainder
My google search came up with quite a few suggestions (REMAINDER, MOD) that
are not available on the iSeries

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com

As an Amazon Associate we earn from qualifying purchases.