daily pastebin goal
92%
SHARE
TWEET

Untitled

a guest Jul 12th, 2018 65 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ID    Color    Ranges (nvarchar2!)
  2. --    -----    -------------------------
  3. 1     Blue     1-9,23.5-25.1,27.11,99.14
  4. 2     Red      4
  5. 3     Green    4.44-5.3
  6. 4     Black    18-22,101
  7.    
  8. SQL> select id
  9.   2         , color
  10.   3         , to_number(case when ranges like '%-%' then regexp_substr(ranges,'[^-]+',1,1) else ranges end) low_value
  11.   4         , to_number(case when ranges like '%-%' then regexp_substr(ranges,'[^-]+',1,2) else ranges end) high_value
  12.   5      from colors
  13.   6     model
  14.   7           return updated rows
  15.   8           partition by (id,color)
  16.   9           dimension by (0 i)
  17.  10           measures (ranges,nvl(length(regexp_replace(ranges,'[^,]')),0) + nvl2(ranges,1,0) as number_of_parts)
  18.  11           ( ranges[for i from 1 to number_of_parts[0] increment 1]
  19.  12             = regexp_substr(ranges[0],'[^,]+',1,cv(i))
  20.  13           )
  21.  14  /
  22.  
  23.                   ID COLOR            LOW_VALUE           HIGH_VALUE
  24. -------------------- ----- -------------------- --------------------
  25.                    2 Red                      4                    4
  26.                    1 Blue                     1                    9
  27.                    1 Blue                  23.5                 25.1
  28.                    1 Blue                 27.11                27.11
  29.                    1 Blue                 99.14                99.14
  30.                    4 Black                   18                   22
  31.                    4 Black                  101                  101
  32.                    3 Green                 4.44                  5.3
  33.  
  34. 8 rows selected.
  35.    
  36. function value_included (p_value in number, p_ranges in varchar2)
  37. return number
  38. is
  39.     l_ranges_tab apex_application_global.vc_arr2;
  40.     l_values_tab apex_application_global.vc_arr2;
  41.     l_retval number := 0;
  42. begin
  43.     l_ranges_tab := apex_util.string_to_table (p_ranges, ',');
  44.     for i in 1..l_ranges_tab.count loop
  45.          l_values_tab := apex_util.string_to_table (l_ranges_tab(i), '-');
  46.          if l_values_tab.count = 1 then
  47.              if p_value = l_values_tab(1) then
  48.                  l_retval := 1;
  49.                  exit;
  50.              end if;
  51.          else
  52.              if p_value between l_values_tab(1) and l_values_tab(2) then
  53.                  l_retval := 1;
  54.                  exit;
  55.              end if;
  56.          end if;
  57.     end loop;
  58.     return l_retval;
  59. end;
  60.    
  61. select color from colors where value_included(5, ranges);
  62.    
  63. function range_overlap (p_from in number, p_to in number, p_ranges in varchar2)
  64. return number
  65. is
  66.     l_ranges_tab apex_application_global.vc_arr2;
  67.     l_values_tab apex_application_global.vc_arr2;
  68.     l_retval number := 0;
  69. begin
  70.     l_ranges_tab := apex_util.string_to_table (p_ranges, ',');
  71.     for i in 1..l_ranges_tab.count loop
  72.          l_values_tab := apex_util.string_to_table (l_ranges_tab(i), '-');
  73.          if l_values_tab.count = 1 then
  74.              if l_values_tab(1) between p_from and p_to then
  75.                  l_retval := 1;
  76.                  exit;
  77.              end if;
  78.          else
  79.              if p_to >= l_values_tab(1) and p_from <= l_values_tab(2) then
  80.                  l_retval := 1;
  81.                  exit;
  82.              end if;
  83.          end if;
  84.     end loop;
  85.     return l_retval;
  86. end;
  87.    
  88. create or replace type range_type as object (range_from number, range_to number);
  89. create or replace type range_table as table of range_type;
  90.    
  91. create or replace function range_to_nested_table(i_ranges in varchar2)
  92.   return range_table pipelined is
  93.  
  94.   thisRange varchar2(4000);
  95.   loop_counter number := 1;
  96.  
  97.   output_row range_type;
  98.  
  99. begin
  100.  
  101.   loop
  102.     thisRange := rtrim(regexp_substr(i_ranges, '[^,]*,?', 1, loop_counter), ',');
  103.  
  104.     exit when thisRange is null;
  105.     loop_counter := loop_counter + 1;
  106.  
  107.     if thisRange like '%-%' then
  108.       output_row := range_type(to_number(regexp_substr(thisRange, '[^-]*', 1, 1)),
  109.                                to_number(regexp_substr(thisRange, '[^-]*(-|$)', 1, 2)));
  110.     else
  111.       output_row := range_type(to_number(thisRange), to_number(thisRange));
  112.     end if;
  113.  
  114.     pipe row(output_row);
  115.  
  116.   end loop;
  117.  
  118.   RETURN;
  119.  
  120. end;
  121.    
  122. with my_sample_data as (
  123.   select 1 as id, 'Blue' as color, '1-9,23.5-25.1,27.11,99.14' as range from dual union all
  124.   select 2 as id, 'Red' as color, '4' as range from dual union all
  125.   select 3 as id, 'Green' as color, '4.44-5.3' as range from dual union all
  126.   select 4 as id, 'Black' as color, '18-22,101' as range from dual
  127. )
  128. select id, color, range, b.*
  129. from my_sample_data a, table(range_to_nested_table(a.range)) b
  130. where 5 between b.range_from and b.range_to
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top