CODEWITHSUNDEEP
sqloracle-databaseplsql
Devendra S
Devendra S

Reputation: 11

Exclude a range of values that are encompassed by another range, using variables

I have 2 variables that describe a range; from and to. However, there is some overlap between the ranges defined in my code. I would like to exclude one value from the range, by using variables because I am passing these variables as a parameter in the package to call other programs.

For example, if I have this code:

 IF name = 'AA'
    THEN
       from := '101-0000-0000';
       to := '101-9999-9999';
    ELSIF name = 'BB'
    THEN
       from := '200-0000-0000';
       to := '200-9999-9999';
    ELSIF name = 'CC'
    THEN
       from := '100-0000-0000';
       to := '120-9999-0000';
    ELSIF name = 'DD'
    THEN
       from := '400-0000-0000';
       to := '402-9999-9999';
    END IF;

I want to exclude the 101-****-**** values from the name = 'CC' range because they are already in use by the name = 'AA' range. The CC from value is 100-0000-0000 and to value is 120-9999-9999, which completely covers 101-****-****.

Upvotes: 1

Views: 245

Answers (1)

Ben
Ben

Reputation: 52893

You can't do that in the manner which you'd like to. You're going to have to have more variables, for instance from2 and to2 (or put them in an array). If you need to exclude 2 ranges then you're going to need 3 sets of variables, etc. This method is, therefore, not extensible and leads only to unmaintainable code bloat. I'd argue that you're already getting there.

You do, however, have a database to hand. Use it for what it's good at. Create a table.

create table name_ranges ( 
     name varchar2(2) not null
   , min_value varchar2(13) not null
   , max_value varchar2(13) not null
   , constraint pk_name_ranges primary key (name, min_value)
   , constraint uk_name_ranges_min unique (min_value)
   , constraint uk_name_ranges_max unique (max_value)
   , constraint ck_name_ranges_min_max check (min_value <= max_value)
     );

insert into name_ranges values ('AA', '101-0000-0000', '101-9999-9999');
insert into name_ranges values ('BB', '200-0000-0000', '200-9999-9999');
insert into name_ranges values ('CC', '100-0000-0000', '100-9999-9999');
insert into name_ranges values ('CC', '102-0000-0000', '120-9999-9999');
insert into name_ranges values ('DD', '400-0000-0000', '402-9999-9999');

I've left these numbers as VARCHARs; but I'd consider changing them to NUMBERs. The separators can be added in when you're required to display the data, but they just make managing numbers more difficult for the rest of the time. Though I've added a load of constraints, there's no way of guaranteeing that there are no overlapping ranges; you're going to have to be as careful as you are when declaring these in code.

Now, whenever you need to select data from another table based on these ranges you can join with a normal non-equi join:

select my.*
  from my_table my
  join name_ranges nr
    on my.name = nr.range
   and my.column_name between nr.min_value and nr.max_value

This has the benefit of simplifying the code and reducing the volume. It also means that if you ever need to change anything you only have to change a table. Nothing else. It saves so much hassle in the longer run it's unbelievable. If your reasoning for some of the choices you've made for your ranges are unclear add a DESCRIPTION column to the table that you populate with free-text to explain them.

If you absolutely have to have these as variables in code then declare a type that is a table of the actual table and then just put them into that.

declare
   type t__name_ranges is table of name_ranges index by binary_integer;
   t_name_ranges t__name_ranges;
begin
   select * bulk collect into t_name_ranges
     from name_ranges;

   -- do_something
end;

Upvotes: 1

Related Questions