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

    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
        • 1. Re: Logic for Best Matching from possible row...
          523861
          given that test data, could you show us what your expected output would be?
          • 2. Re: Logic for Best Matching from possible row...
            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
            • 3. Re: Logic for Best Matching from possible row...
              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
              • 4. Re: Logic for Best Matching from possible row...
                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
                • 5. Re: Logic for Best Matching from possible row...
                  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.
                  • 6. Re: Logic for Best Matching from possible row...
                    user481231
                    Thank You very much Michael. Its working.

                    Thanks a Lot
                    • 7. Re: Logic for Best Matching from possible row...
                      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
                      • 8. Re: Logic for Best Matching from possible row...
                        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
                        • 9. Re: Logic for Best Matching from possible row...
                          user481231
                          Thanks AP and Aketi Jyuuzou.
                          • 10. Re: Logic for Best Matching from possible row...
                            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