RE: Embedded SQL "Nearest number" function?

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Thursday, December 05, 2013 4:29 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: Embedded SQL "Nearest number" function?

Probably not the most efficient way to do this, but...

SELECT my.*, abs( DBVAL - :TestVal ) as delta
FROM MYILE my
ORDER BY delta
fetch first row only

-Eric

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Thursday, December 05, 2013 2:32 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Embedded SQL "Nearest number" function?

(Please feel free to ignore this if you've got real work to do)

I know I can solve this by fetching through a cursor, but I thought I'd
toss this up for someone to introduce me to a function that I've not
yet discovered:

I need the row where a numeric column contains a number closest to
(either greater or less than) a specified value.
Example: I want the row where ColumnA is closest to 1580.
Row# ColumnA
1 2016
2 1527
3 1874
4 4769
5 1600
6 158

Since there is no row with '1580', I would accept row 5, which is the
closest. If there were two rows where the difference from the target
value is the same (like if row 7 was added where ColumnA was 1560), I'd
prefer the larger ColumnA value.


Michael Koester