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,
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
--
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
