Re: Embedded SQL "Nearest number" function?

Lots of times such, "sequential number" problems are lots easier (and
faster) if you have a numbers table....

create table wiltc/numbers as (
WITH temp1 (num) AS
(VALUES (0)
UNION ALL
SELECT num + 1
FROM temp1
WHERE num < 10000000
)
SELECT num
FROM temp1
) with data

alter table wiltc/numbers
add primary key (NUM)

Now your query looking for the value closest to 1875 (using Chuck's example
data):
select *
from wiltc/numbers
left outer join qtemp/filefc
on num = columna
where num >= 1875
and columna is not null
order by num
fetch first row only

HTH,
Charles



On Fri, Dec 6, 2013 at 2:32 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 05-Dec-2013 12:31 -0800, Koester, Michael wrote:

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.

FWiW, this topic is not specific to the RPG; i.e. could just as well
have been started on the midrange-l instead.

No mention of which row to choose if there are multiple equidistant
values found. Or maybe there is a unique index on ColumnA?

If there is a keyed-access-path\index for the ColumnA, then the
following query should enable the query engine to avoid processing all
rows of the table; i.e. the use of two index [index-only seems likely]
accesses to find the specific values of ColumnA in the file that are
either equal to the input value or the nearest lower value or the
nearest higher value, and then retrieval by that index for the specific
rows of those eligible. Given unique values across ColumnA there could
be only one or two eligible rows possible. But if not unique, then
there could be many possible matches; ordering by the Row# column might
be necessary as I alluded originally above.?

Setup used to test; too small for costing tending toward an AccPth
implementation:

create table qtemp/filefc (row# dec(2), columna dec)
;
insert into qtemp/filefc values
( 1, 2016 )
,( 2, 1527 )
,( 3, 1874 )
,( 4, 4769 )
,( 5, 1600 )
,( 6, 158 )
,( 7, 1876 )
,( 8, 1876 )
,( 9, 1876 )
,( 10, 1513 )
; -- 10 rows inserted in FILEFC in QTEMP.
create index filefcix on qtemp/filefc (columna desc)
; -- Index FILEFCIX created in QTEMP on table FILEFC in QTEMP.

Test with input values, to include boundaries, equal, equidistant,
equidistant and duplicates:

:nearToVal = 5000 4769 3000 1520 1776 1876 1875 1580 0200 158 003
:resultVal = 4769 4769 2016 1527 1874 1876 1876 1600 0158 158 158
:resultRow#= 4 4 1 2 3 7± 7± 5 6 6 6
EligibleRows= 1 1 2 2 2 3 4 2 2 1 1
OtherRowVal= - - 4769 1513 1600 1876 1874 1527 1513 - -
±the result is unpredictable, without ordering on Row#

If there is no constraint preventing negative values, some tests with
negative values perhaps should be added.

While the following queries look and indeed are, _more complex_ than
the other query already offered in the thread, I expect if there is a
large amount of data in the file, then the implementation [access plan]
of the following queries might enable either query to perform much
faster than a query that both selects and orders on an expression over
all rows. The following queries should perform the expression [absolute
value of the result of the column-value minus the input value] and
ordering only on the already-retrieved data from the [likely just one or
two] rows rather than having to perform that expression and collation by
its result for every row:


select y.*
from
( values( (select min(s.columna) from qtemp/filefc s
where s.columna >= :nearToVal )
, (select max(s.columna) from qtemp/filefc s
where s.columna <= :nearToVal )
) as x ( higherVal, lesserVal ) /* don't let min\max fool you */
, lateral /* implied cross join with the above one-row table */
( select s.* , abs( s.columna - :nearToVal ) as diffVal
from qtemp/filefc s
where s.columna in ( x.lesserVal, x.higherVal )
) y
order by diffVal asc
, columna desc
/* , row# desc -- latest row? */
fetch first 1 row only /* implicitly then: optimize for 1 row */


with
minmax (minmaxVal) as
( (select min(s.columna) from qtemp/filefc s
where s.columna >= :nearToVal )
union (select max(s.columna) from qtemp/filefc s
where s.columna <= :nearToVal )
) /* UNION ALL acceptable too; but for consistent */
/* test results to matrix above, without fetch first */
select s.* , abs( s.columna - :nearToVal ) as diffVal
from minmax x
left join qtemp/filefc s
on minmaxVal = s.columna
where minmaxVal is not null /* optional; but for consistent */
/* test results to matrix above, without fetch first */
order by diffVal asc
, columna desc
/* , row# desc -- latest row? */
fetch first 1 row only /* implicitly then: optimize for 1 row */


--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.