Subject: RE: In SQl, how do you determine if fielda divided by field b has any remainder? From: "Birgitta Hauser" Date: Wed, 15 Jan 2020 17:26:01 +0100 List-archive: List-help: List-id: Midrange Systems Technical Discussion List-post: List-subscribe: , List-unsubscribe: ,

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

As an Amazon Associate we earn from qualifying purchases.