Oracle PLSQL - How to get the lowest and highest value from a list of values in a range?

Asked

Viewed 97 times

0

CREATE TABLE TABELA_PRAZO
(
    CEP_INICIO NUMBER(8) NOT NULL, 
    CEP_FIM NUMBER(8) NOT NULL,
    PRAZO NUMBER(3) NOT NULL
);

insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (1000000, 1000005, 5);
insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (510101, 510104, 4);
insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (510000, 510067, 4);
insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (510068, 510100, 3);
insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (1000006, 10000010, 5);
insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (810000, 810001, 5);
insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (810002, 810003, 5);
insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (810004, 810005, 5);

The table describes the deadlines that should be used by a freight calculation system, given a range of ZIP CODE (zip code range is an interval between CEP_INICIO and CEP_FIM, both included in the range). Here is a small example of the populated table:

CEP_INICIO   CEP_FIM   PRAZO
1000000      1000005   5
510101       510104    4
510000       510067    4
510068       510100    3
1000006      10000010  5
810000       810001    5
810002       810003    5
810004       810005    5

Having this, how will I write a query or Procedure in Oracle that unite the records whose zip code bands with same term are considered "neighbors"?
Two CEP bands are considered to be "neighbours" when the CEP_FIM of the first adding 1 is equal CEP_INICIO of the second, eambas have the same deadline?

In the above case, the outcome of the proceedings would be:

CEP_INICIO CEP_FIM  PRAZO
1000000    10000010 5
510101     510104   4
510000     510067   4
510068     510100   3
810000     810005   5

All right, all right, all right.

oracle pl-sql

  • what you already have of code?

  • This idea of neighboring zip code "+1" should not work, I believe, because it has neighboring intervals larger than one. Is there a guarantee that there are no overlapping bands? What is the purpose of this ? Reduce the number of records ? Only display in a query ?

  • So, the neighboring intervals larger than one are of different tracks, the object proposed in this my doubt is to obtain in a single line the smallest and largest number of zip code of a band that is completed within the same period, as in the example: Cep 810000 goes up to 810005 in 3 bands of the same term. I’m cracking my head here but I can’t think of a solution to.

  • But do you "dry" the table or just the query output ? Have you tried something like cep_inicio+1=cep_fim on Join ? I still do not understand the "problem" because the objective of the table is to calculate the term and I do not believe that the number of lines interferes much in this.

1 answer

0


I was able to develop a solution, however, at the operational level, it may not have the desired performance, but the low scale meets 100%.

create or replace procedure insert_compactado is
  cursor c_1 is
    select * from TABELA_PRAZO order by cep_inicio asc;
  r_1        c_1%rowtype;
  cep_min    NUMBER(8);
  cep_max    NUMBER(8);
  cep_inicio NUMBER(8);
  prazo      number(1);
  vcount      number(3);
begin
  cep_min    := null;
  cep_max    := null;
  cep_inicio := null;
  prazo      := null;
  open c_1;
  fetch c_1
    into r_1;
  while c_1%found loop
    cep_min := r_1.cep_inicio;
    cep_max := r_1.cep_fim;
    prazo   := r_1.prazo;
    vcount := null;
    FETCH c_1
      INTO r_1;
      while r_1.cep_inicio = cep_max + 1 and r_1.prazo = prazo loop
        cep_max := r_1.cep_fim;
        FETCH c_1 INTO r_1;
        while cep_inicio = r_1.cep_fim - 1 and r_1.prazo = prazo and cep_max < r_1.cep_fim loop
          cep_inicio := cep_min;
          cep_max := r_1.cep_fim;
        end loop;
      end loop;
    begin
      select nvl(count(1),0)
        into vcount
        from TABELA_PRAZO_COMPACTADO t
       where t.cep_inicio = cep_min
         and t.cep_fim = cep_max
         and t.prazo = prazo;
    end;
    if vcount = 0 then 
      begin
        insert into TABELA_PRAZO_COMPACTADO(cep_inicio,
                                            cep_fim,
                                            prazo)
                                     values(cep_min,
                                            cep_max,
                                            prazo);
      end;           
    end if;
  end loop;
  close c_1;
  commit;
end;

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.