10 Replies Latest reply on Dec 17, 2010 12:59 PM by Aketi Jyuuzou

# Logic for Best Matching from possible row...

Hi All,

For example I have below columns and values in my test table

create table test(level1 number,level2 number,level3 number,level4 number,level5 number,level6 number,level7 number,level8 number)

insert into test values(2,3,4,5,6,7,8,9)
insert into test values(12,13,14,15,16,17,18,19)
insert into test values(5,6,7,8,9,11,13,15)
insert into test values(7,8,9,10,11,12,13,14)
insert into test values(9,10,13     ,14,15,16,17,18)
insert into test values(11,22,27,28,29,30,31,32)

select * from test;

2     3     4     5     6     7     8     9 output should be 9
12     13     14     15     16     17     18     19 output should be 12
5     6     7     8     9     11     13     15 output should be 13
7     8     9     10     11     12     13     14 output should be 12
9     10     13     14     15     16     17     18 output should be 13
11     22     27     28     29     30     31     32 output should be 22

when I input a number Ex:12 for each row If there is any column matching exactly it should be dispalyed
IF NOT the most possible highest matching number for that row should be displayed.

is there any in built functions can we use for this ? If anyboyd having this type of logic
please send to me. I think its similar to vlookup in Excel.

Devender
• ###### 1. Re: Logic for Best Matching from possible row...
given that test data, could you show us what your expected output would be?
• ###### 2. Re: Logic for Best Matching from possible row...
Query become too big

use this function to get your desired output

You can get the least value of the
``````select LEAST(ABS(LEVEL1-12),ABS(LEVEL2-12),ABS(LEVEL3-12),ABS(LEVEL4-12),ABS(LEVEL5-12),ABS(LEVEL6-12),ABS(LEVEL7-12),ABS(LEVEL8-12)) Least_Val from test
/

LEAST_VAL
----------
3                    ----------> 12 + or - 3 = 9 or 15
0                    ----------> 12 + or - 0 =12
1                    ----------> 12 + or - 1 = 11 or 13
0
1
1``````
• ###### 3. Re: Logic for Best Matching from possible row...
Check this
``````select DECODE(ABS(LEVEL1-12),Least_Val,LEVEL1,
DECODE(ABS(LEVEL2-12),Least_Val,LEVEL2,
DECODE(ABS(LEVEL3-12),Least_Val,LEVEL3,
DECODE(ABS(LEVEL4-12),Least_Val,LEVEL4,
DECODE(ABS(LEVEL5-12),Least_Val,LEVEL5,
DECODE(ABS(LEVEL6-12),Least_Val,LEVEL6,
DECODE(ABS(LEVEL7-12),Least_Val,LEVEL7,
DECODE(ABS(LEVEL8-12),Least_Val,LEVEL8,
NULL))))))))     Required_Val
FROM
(
select LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6,LEVEL7,LEVEL8, LEAST(ABS(LEVEL1-12),ABS(LEVEL2-12),ABS(LEVEL3-12),ABS(LEVEL4-12),ABS(LEVEL5-12),ABS(LEVEL6-12),ABS(LEVEL7-12),ABS(LEVEL8-12)) Least_Val from test
)
/
REQUIRED_VAL
------------
9
12
11
12
13
11``````
• ###### 4. Re: Logic for Best Matching from possible row...
Hi Lokanath,

Thanks for your time and reply. Did you observe its not exactly coming what I
was expecting in ouput.

2 3 4 5 6 7 8 9      for this row     output should be 9 where as with ur query output is 9 Correct
12 13 14 15 16 17 18 19 for this row     output should be 12 where as with ur query output is 12 Correct
5 6 7 8 9 11 13 15      for this row     output should be 13 where as with ur query output is 11 Wrong
7 8 9 10 11 12 13 14      for this row     output should be 12 where as with ur query output is 12 Correct
9 10 13 14 15 16 17 18 for this row     output should be 13 where as with ur query output is 13 Correct
11 22 27 28 29 30 31 32 for this row     output should be 22 where as with ur query output is 11 Wrong

Can you have a look one more time and see my expected output matches.

Thank you very much.
Devender
• ###### 5. Re: Logic for Best Matching from possible row...
Introducing an additional ID column to identify the rows:
``````SQL> create table test(id number, level1 number,level2 number,level3 number,level4 number,level5 number,level6 number,level7 number,level8 number)
/
Table created.

SQL> insert into test values(1,2,3,4,5,6,7,8,9)
/
1 row created.

SQL> insert into test values(2,12,13,14,15,16,17,18,19)
/
1 row created.

SQL> insert into test values(3,5,6,7,8,9,11,13,15)
/
1 row created.

SQL> insert into test values(4,7,8,9,10,11,12,13,14)
/
1 row created.

SQL> insert into test values(5,9,10,13 ,14,15,16,17,18)
/
1 row created.

SQL> insert into test values(6, 11,22,27,28,29,30,31,32)
/
1 row created.

SQL> select distinct
id "id",
nvl (min (case when column_value - 12 >= 0 then column_value end) over (partition by id),
max (case when column_value - 12 < 0 then column_value end) over (partition by id))
"level"
from test,
table (sys.odcinumberlist (level1,
level2,
level3,
level4,
level5,
level6,
level7,
level8))
order by id
/
id      level
---------- ----------
1          9
2         12
3         13
4         12
5         13
6         22

6 rows selected.``````
• ###### 6. Re: Logic for Best Matching from possible row...
Thank You very much Michael. Its working.

Thanks a Lot
• ###### 7. Re: Logic for Best Matching from possible row...
I have made without UnPivot solution B-)
``````with test(L1,L2,L3,L4,L5,L6,L7,L8) as(
select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
select 12,13,14,15,16,17,18,19 from dual union all
select  5, 6, 7, 8, 9,11,13,15 from dual union all
select  7, 8, 9,10,11,12,13,14 from dual union all
select  9,10,13,14,15,16,17,18 from dual union all
select 11,22,27,28,29,30,31,32 from dual)
select L1,L2,L3,L4,L5,L6,L7,L8,
case when 12 in(L1,L2,L3,L4,L5,L6,L7,L8) then 12
when 12 > any(L1,L2,L3,L4,L5,L6,L7,L8)
then Least(case when L1 < 12 then maxVal else L1 end,
case when L2 < 12 then maxVal else L2 end,
case when L3 < 12 then maxVal else L3 end,
case when L4 < 12 then maxVal else L4 end,
case when L5 < 12 then maxVal else L5 end,
case when L6 < 12 then maxVal else L6 end,
case when L7 < 12 then maxVal else L7 end,
case when L8 < 12 then maxVal else L8 end)
else maxVal end as ExtVal
from (select L1,L2,L3,L4,L5,L6,L7,L8,
greatest(L1,L2,L3,L4,L5,L6,L7,L8) as maxVal
from test);

L1  L2  L3  L4  L5  L6  L7  L8  ExtVal
--  --  --  --  --  --  --  --  ------
2   3   4   5   6   7   8   9       9
12  13  14  15  16  17  18  19      12
5   6   7   8   9  11  13  15      13
7   8   9  10  11  12  13  14      12
9  10  13  14  15  16  17  18      13
11  22  27  28  29  30  31  32      22``````
• ###### 8. Re: Logic for Best Matching from possible row...
Like this:
``````with t(L1,L2,L3,L4,L5,L6,L7,L8) as(
select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
select 12,13,14,15,16,17,18,19 from dual union all
select  5, 6, 7, 8, 9,11,13,15 from dual union all
select  7, 8, 9,10,11,12,13,14 from dual union all
select  9,10,13,14,15,16,17,18 from dual union all
select 11,22,27,28,29,30,31,32 from dual)
SELECT (CASE WHEN 12 >= max_Value THEN max_value
WHEN 12 < max_value AND 12 > ls_val THEN
(CASE WHEN l2-12 >= 0 THEN l2
WHEN l3-12 >= 0 THEN l3
WHEN l4-12 >= 0 THEN l4
WHEN l5-12 >= 0 THEN l5
WHEN l6-12>= 0 THEN l6
WHEN l7-12 >= 0 THEN l7
END)
WHEN 12 = ls_val THEN ls_Val END) out_val FROM
(select l1,l2,l3,l4,l5,l6,l7,l8,GREATEST(l1,l2,l3,l4,l5,l6,l7,l8) max_value
, LEAST(l1,l2,l3,l4,l5,l6,l7,l8) ls_val from t)

out_val
----------
9
12
13
13
13
22

SQL>
with t(L1,L2,L3,L4,L5,L6,L7,L8) as(
select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
select 12,13,14,15,16,17,18,19 from dual union all
select  5, 6, 7, 8, 9,11,13,15 from dual union all
select  7, 8, 9,10,11,12,13,14 from dual union all
select  9,10,13,14,15,16,17,18 from dual union all
select 11,22,27,28,29,30,31,32 from dual)
SELECT (CASE WHEN 16 >= max_Value THEN max_value
WHEN 16 < max_value AND 16 > ls_val THEN
(CASE WHEN l2-16 >= 0 THEN l2
WHEN l3-16 >= 0 THEN l3
WHEN l4-16 >= 0 THEN l4
WHEN l5-16 >= 0 THEN l5
WHEN l6-16 >= 0 THEN l6
WHEN l7-16 >= 0 THEN l7
END)
WHEN 16 = ls_val THEN ls_Val END) out_val FROM
(select l1,l2,l3,l4,l5,l6,l7,l8,GREATEST(l1,l2,l3,l4,l5,l6,l7,l8) max_value
, LEAST(l1,l2,l3,l4,l5,l6,l7,l8) ls_val from t)

out_val
----------

9
16
15
14
16
22``````
• ###### 9. Re: Logic for Best Matching from possible row...
Thanks AP and Aketi Jyuuzou.
• ###### 10. Re: Logic for Best Matching from possible row...
I like sys.odciNumberList :-)
``````with test(L1,L2,L3,L4,L5,L6,L7,L8) as(
select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
select 12,13,14,15,16,17,18,19 from dual union all
select  5, 6, 7, 8, 9,11,13,15 from dual union all
select  7, 8, 9,10,11,12,13,14 from dual union all
select  9,10,13,14,15,16,17,18 from dual union all
select 11,22,27,28,29,30,31,32 from dual)
select L1,L2,L3,L4,L5,L6,L7,L8,
nvl(min(case when 12 <= column_value
then column_value  end),
max(column_value)) as ExtVal
from test,
Table(sys.odciNumberList(L1,L2,L3,L4,L5,L6,L7,L8))
group by L1,L2,L3,L4,L5,L6,L7,L8
order by L1,L2,L3,L4,L5,L6,L7,L8;

L1  L2  L3  L4  L5  L6  L7  L8  ExtVal
--  --  --  --  --  --  --  --  ------
2   3   4   5   6   7   8   9       9
5   6   7   8   9  11  13  15      13
7   8   9  10  11  12  13  14      12
9  10  13  14  15  16  17  18      13
11  22  27  28  29  30  31  32      22
12  13  14  15  16  17  18  19      12``````