RE: Embedded SQL "Nearest number" function?

That's way more perfect than the approaches I've been pursuing (tunnel vision is so limiting).
By changing "ORDER BY delta" to "ORDER BY delta, DBVAL desc", I get my "tie goes to the larger" requirement!

Many thanks, Eric!
-- mk

-----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