Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Range function

Range function

From: Yechiel Adar <adar666_at_inter.net.il>
Date: Fri, 17 Mar 2006 11:59:05 +0200
Message-ID: <441A8869.8000209@inter.net.il>


I got several requests for the function so I decided to post it. I do not remember what ts_ranges does, probably a copy for tests.

This was written years ago so there must be room for improvements. It does mostly comparison so compiling it will bring great benefit.

The function gets 2 parameters:
1) ranges list. from1_to1,from2_to2,..... 2) Value to check.

For example: select Range('0-100,101-200,201-300',balance) from account; will divide the records to negative balance, one of the ranges or more then 300.

I am keeping the last 2 range lists in memory to avoid parsing of the range list each time.

As you can see you can use and copy it, but please retain the copyright.

If you make any enhancements please tell me so I can enhance my copy.

USE IT ON YOUR OWN RISK. CREATE OR REPLACE PACKAGE "RANGE" AS
--

  FUNCTION RANGES(RANGE_LIST IN VARCHAR2 , FORMULA_V IN NUMBER) RETURN VARCHAR2 PARALLEL_ENABLE;
  FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2 , FORMULA_V IN NUMBER) RETURN VARCHAR2 PARALLEL_ENABLE;
PRAGMA RESTRICT_REFERENCES (RANGES, WNDS, RNDS); END RANGE;
/

CREATE OR REPLACE PACKAGE BODY RANGE IS
-- PRAGMA SERIALLY_REUSABLE;
FUNCTION RANGES(RANGE_LIST IN VARCHAR2 ,FORMULA_V IN NUMBER) RETURN VARCHAR2
    IS
list_p VARCHAR2(300);
obligo_p NUMBER;
len NUMBER;
s1 VARCHAR2(300);
BEGIN
IF SAVE_PARAM = RANGE_LIST THEN
 CURRENT_PTR := current_ptr;
ELSIF SAVE_PARAM1 = RANGE_LIST THEN
 SAVE_PARAM := SAVE_PARAM1;
 FOR I IN 1..NO_OF_RANGES1 LOOP
R_LOW(I) := R_LOW1(I);
R_HIGH(I) := R_HIGH1(I);
 END LOOP;
 NO_OF_RANGES := NO_OF_RANGES1;
ELSIF SAVE_PARAM2 = RANGE_LIST THEN
-- dbms_output.put_line('2 ' || current_ptR);
 SAVE_PARAM := SAVE_PARAM2;
 FOR I IN 1..NO_OF_RANGES2 LOOP
R_LOW(I) := R_LOW2(I);
R_HIGH(I) := R_HIGH2(I);
 END LOOP;
 NO_OF_RANGES := NO_OF_RANGES2;
ELSE
-- dbms_output.put_line('1 ' || CUrrent_ptR);
  SAVE_PARAM := range_list;
  list_p := range_list;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW(I) := NULL;
R_HIGH(I) := NULL;
END LOOP;
NO_OF_RANGES := 0;
  FOR i IN 1..LENGTH(list_p) LOOP

      len := INSTR(list_p,',');
IF len IS NULL THEN

   EXIT;
END IF;
no_of_ranges := no_of_ranges + 1;
s1 := SUBSTR(list_p,1,len);
list_p := SUBSTR(list_p,len+1);

      len       := INSTR(s1,'_');
      R_low(i)  := SUBSTR(s1,1,len-1);
      R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);
  END LOOP;
IF SAVE_PARAM1 = NULL OR CURRENT_PTR = 2 THEN   SAVE_PARAM1 := SAVE_PARAM;
  FOR I IN 1..NO_OF_RANGES LOOP
   R_LOW1(I) := R_LOW(I);
   R_HIGH1(I) := R_HIGH(I);
  END LOOP;
  NO_OF_RANGES1:= NO_OF_RANGES;
  CURRENT_PTR := 1;
ELSE
  SAVE_PARAM2 := SAVE_PARAM;
  FOR I IN 1..NO_OF_RANGES LOOP
   R_LOW2(I) := R_LOW(I);
   R_HIGH2(I) := R_HIGH(I);
  END LOOP;
  NO_OF_RANGES2 := NO_OF_RANGES;
  CURRENT_PTR := 2;
END IF;
END IF;
-- dbms_output.put_line(current_ptr || save_param);
obligo_p:=FORMULA_V;
 IF obligo_p < R_low(1) THEN
RETURN ' 0 ) <' ||R_low(1);
 END IF;
 FOR j IN 1..no_of_ranges LOOP
   IF obligo_p BETWEEN R_LOW(j) AND R_high(j) THEN    IF j < 10 THEN

         RETURN ' '||j||' ) '||R_low(j)||'-'||R_high(j); ELSE
         RETURN j||' ) '||R_low(j)||'-'||R_high(j); END IF;
   END IF;
 END LOOP;
 IF obligo_p > R_high(no_of_ranges) THEN

    IF no_of_ranges+1 < 9 THEN
    RETURN ' '||no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';     ELSE
    RETURN no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';  END IF;
 END IF;
 RETURN obligo_p || ' - NOT IN RANGE !!'; END RANGES; FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2 ,FORMULA_V IN NUMBER) RETURN VARCHAR2
    IS
list_p VARCHAR2(300);
obligo_p NUMBER;
len NUMBER;
s1 VARCHAR2(300);
BEGIN
IF SAVE_PARAM = RANGE_LIST THEN
 CURRENT_PTR := current_ptr;
ELSIF SAVE_PARAM1 = RANGE_LIST THEN
 SAVE_PARAM := SAVE_PARAM1;
 FOR I IN 1..NO_OF_RANGES1 LOOP
R_LOW(I) := R_LOW1(I);
R_HIGH(I) := R_HIGH1(I);
 END LOOP;
 NO_OF_RANGES := NO_OF_RANGES1;
ELSIF SAVE_PARAM2 = RANGE_LIST THEN
-- dbms_output.put_line('2 ' || current_ptR);
 SAVE_PARAM := SAVE_PARAM2;
 FOR I IN 1..NO_OF_RANGES2 LOOP
R_LOW(I) := R_LOW2(I);
R_HIGH(I) := R_HIGH2(I);
 END LOOP;
 NO_OF_RANGES := NO_OF_RANGES2;
ELSE
-- dbms_output.put_line('1 ' || CUrrent_ptR);
  SAVE_PARAM := range_list;
  list_p := range_list;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW(I) := NULL;
R_HIGH(I) := NULL;
END LOOP;
NO_OF_RANGES := 0;
  FOR i IN 1..LENGTH(list_p) LOOP

      len := INSTR(list_p,',');
IF len IS NULL THEN

   EXIT;
END IF;
no_of_ranges := no_of_ranges + 1;
s1 := SUBSTR(list_p,1,len);
list_p := SUBSTR(list_p,len+1);

      len       := INSTR(s1,'_');
      R_low(i)  := SUBSTR(s1,1,len-1);
      R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);
  END LOOP;
IF SAVE_PARAM1 = NULL OR CURRENT_PTR = 2 THEN   SAVE_PARAM1 := SAVE_PARAM;
  FOR I IN 1..NO_OF_RANGES LOOP
   R_LOW1(I) := R_LOW(I);
   R_HIGH1(I) := R_HIGH(I);
  END LOOP;
  NO_OF_RANGES1:= NO_OF_RANGES;
  CURRENT_PTR := 1;
ELSE
  SAVE_PARAM2 := SAVE_PARAM;
  FOR I IN 1..NO_OF_RANGES LOOP
   R_LOW2(I) := R_LOW(I);
   R_HIGH2(I) := R_HIGH(I);
  END LOOP;
  NO_OF_RANGES2 := NO_OF_RANGES;
  CURRENT_PTR := 2;
END IF;
END IF;
-- dbms_output.put_line(current_ptr || save_param);
obligo_p:=FORMULA_V;
 IF obligo_p < R_low(1) THEN
RETURN ' 0 ) <' ||R_low(1);
 END IF;
 FOR j IN 1..no_of_ranges LOOP
   IF obligo_p BETWEEN R_LOW(j) AND R_high(j) THEN    IF j < 10 THEN

         RETURN ' '||j||' ) '||R_low(j)||'-'||R_high(j); ELSE
         RETURN j||' ) '||R_low(j)||'-'||R_high(j); END IF;
   END IF;
 END LOOP;
 IF obligo_p > R_high(no_of_ranges) THEN

    IF no_of_ranges+1 < 9 THEN
    RETURN ' '||no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';     ELSE
    RETURN no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';  END IF;
 END IF;
 RETURN obligo_p || ' - NOT IN RANGE !!'; END ts_RANGES;

END RANGE ;
/

Adar Yechiel
Rechovot, Israel

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 17 2006 - 03:59:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US