Copy of Oracle materials structure

Asked

Viewed 39 times

2

We have the following material registration structure:

Table

Pk            FK            Filial  Descricao material

Group 

Cod_grupo               
 1             -                1   Materiais de escritório
 2             -                1   EPIS
 3             -                1   impressos

Subgroup 

Cod_Subgrupo         Cod_grupo      
        111             1               1   Acessórios para Computador
        24              2               1   Uniformes
        203             3               1   Jornais e revistas

Class 

   Cod_classe    Cod_Subgrupo       
   1028          111                1   Insumos 
   856           203                1   Jornais e revistas
   18            24                 1   Uniformes

Material 

Cod_material    Cod_classe      
 313131          1028              1    Etiqueta
 313132          1028              1    Fotocondutor
 313133          856               1    Livros
 313134          856               1    Jornais
 313135          18                1    Bota
 313136          18                1    Luva

Based on this structure, I need to replicate the same structure for branch 2. The problem is that the Fks. How could I create a script that copies this structure following the hierarchies of grupo --> sugrupo --> classe --Material ?

sql oracle

  • Roughly mode Insert into select 2 branch , .... from Tabela1 Where branch = 1 . make this sql for all tables in hierarchical order.

1 answer

1

As I do not know the structure of your table, so I implemented according to the understanding of the business rule.

I don’t know how it’s done in your database to sequence each PK, so I did a sequence, based on the largest code in the table:

DECLARE
 I  NUMBER(11);
BEGIN
 SELECT MAX(g.cod_grupo)+1
   INTO I
   FROM grupo g;
 EXECUTE IMMEDIATE ('create sequence seq_grupo start with '||I);   
END;
/

So I created the Insert, which is based on a query, from which basically takes the next code, and makes a decode in the subsidiary, where 1 is replaced by 2.

INSERT INTO grupo
  SELECT seq_grupo.nextval AS cod_grupo
        ,DECODE(g.filial,1,2) AS filial
        ,g.descricao
    FROM grupo g;

You would have to create the structure above, for each table.

I made an example using Oracle livesql, since the sqlfiddle site was giving problem https://livesql.oracle.com/apex/livesql/file/content_E08Y4Z2WO3EWKV63CTNHR1SIL.html

Browser other questions tagged sql oracle

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