Logic for Best Matching from possible row...

user481231

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.

Thanks in advance.
Devender

MichaelS

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.

523861

given that test data, could you show us what your expected output would be?

Lokanath Giri

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

Lokanath Giri

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

user481231

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

MichaelS

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.

user481231

Thank You very much Michael. Its working.

Thanks a Lot

Aketi Jyuuzou

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

737905

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

user481231

Thanks AP and Aketi Jyuuzou.

Aketi Jyuuzou

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