Query returning duplicate values

Question

Query returning duplicate values

Asked 6 years, 11 months ago

Viewed 554 times

2

people I posted here on topic SQL query with duplicate items I just have another question... have a sql:

SELECT 
  TABNOV.CODIGO_ANDAMENTO,
  TABNOV.NUMERO_PROCESSO,
  TABNOV.INSTANCIA,
  TABNOV.DESCRICAO_ANDAMENTO,
  TABNOV.ACESSO_PUBLICO,
  TABNOV.DATA,
  TABNOV.DATA_INCLUSAO,
  TABNOV.HORA,
  TABNOV.EMAIL_ENVIADO,
  TABNOV.CODIGO_PUBLICACAO,
  TABNOV.CODIGO_CONSULTA,
  TABNOV.CODIGO_AUDIENCIA,
  TABNOV.CODIGO_AGENDAMENTO,
  TABNOV.USUARIO_CADASTRO,
  TABNOV.USUARIO_ALTERACAO,
  TABNOV.DATA_ALTERACAO,
  TABNOV.HORA_ALTERACAO,
  TABNOV.OBSERVACAO,
  TABNOV.NOME_IMAGEM_ANDAMENTO,
  TABNOV.NUMERO_PROCESSO_AUX,
  TABNOV.ATO_FATURAVEL
FROM                                                                 
  ( SELECT
      T1.*
    FROM
      ANDAMENTOS_PROCESSUAIS T1
      LEFT JOIN PROCESSOS T2 ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO
    WHERE (T1.NUMERO_PROCESSO_AUX = T2.NUMERO_PROCESSO)
    AND   (T2.FASE_PROCESSO LIKE "ARQUIVADO")
    UNION ALL                                                          
    SELECT                                                             
      T1.*                                                              
    FROM                                                               
      ANDAMENTOS_PROCESSUAIS T1                                         
      LEFT JOIN PROCESSOS T2 ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO 
    WHERE (T1.NUMERO_PROCESSO_AUX = T2.NUMERO_PROCESSO2)               
    AND   (T2.FASE_PROCESSO2 LIKE "ARQUIVADO")
    UNION ALL                                                          
    SELECT                                                             
      T1.*                                                              
    FROM                                                               
      ANDAMENTOS_PROCESSUAIS T1                                         
      LEFT JOIN PROCESSOS T2 ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO 
    WHERE (T1.NUMERO_PROCESSO_AUX = T2.NUMERO_PROCESSO3)               
    AND   (T2.FASE_PROCESSO3 LIKE "ARQUIVADO")                         
    UNION ALL                                                       
    SELECT                                                        
      T1.*                                                              
    FROM                                                               
      ANDAMENTOS_PROCESSUAIS T1                                         
      LEFT JOIN PROCESSOS T2 ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO 
    WHERE (T1.NUMERO_PROCESSO_AUX = T2.NUMERO_PROCESSO4)               
    AND   (T2.FASE_PROCESSO4 LIKE "ARQUIVADO")                         
    UNION ALL                                                          
    SELECT
      T1.*                                                              
    FROM                                                               
      ANDAMENTOS_PROCESSUAIS T1                                         
      LEFT JOIN PROCESSOS T2 ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO 
    WHERE (T1.NUMERO_PROCESSO_AUX = T2.NUMERO_PROCESSO5)               
    AND   (T2.FASE_PROCESSO5 LIKE "ARQUIVADO")                         
    UNION ALL                                                          
    SELECT                                                            
      T1.*                                                              
    FROM                                                               
      ANDAMENTOS_PROCESSUAIS T1                                         
      LEFT JOIN PROCESSOS T2 ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO 
    WHERE (T1.NUMERO_PROCESSO_AUX = T2.NUMERO_PROCESSO6)               
    AND   (T2.FASE_PROCESSO6 LIKE "ARQUIVADO")                         
    UNION ALL                                                          
    SELECT                                                             
      T1.*                                                              
    FROM                                                               
      ANDAMENTOS_PROCESSUAIS T1                                         
      LEFT JOIN PROCESSOS T2 ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO 
    WHERE (T1.NUMERO_PROCESSO_AUX = T2.NUMERO_PROCESSO7)               
    AND   (T2.FASE_PROCESSO7 LIKE "ARQUIVADO")                         
    UNION ALL                                                          
    SELECT                                                             
      T1.*                                                              
    FROM                                                               
      ANDAMENTOS_PROCESSUAIS T1                                         
      LEFT JOIN PROCESSOS T2 ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO 
    WHERE (T1.NUMERO_PROCESSO_AUX = T2.NUMERO_PROCESSO8)               
    AND   (T2.FASE_PROCESSO8 LIKE "ARQUIVADO")) TABNOV

and she actually follows six more Unions only some of the items appears duplicated and at the time of passing the query items to the PK duplicity error table I don’t know what to do because the record on the main table is not duplicated?

Table structure:

CREATE TABLE ANDAMENTOS_PROCESSUAIS (
  CODIGO_ANDAMENTO INTEGER NOT NULL,
  NUMERO_PROCESSO VARCHAR(30) NOT NULL,
  INSTANCIA INTEGER NOT NULL,
  DESCRICAO_ANDAMENTO VARCHAR(200),
  ACESSO_PUBLICO VARCHAR(1) NOT NULL,
  DATA DATE NOT NULL,
  DATA_INCLUSAO DATE,
  HORA VARCHAR(5) NOT NULL,
  EMAIL_ENVIADO VARCHAR(1),
  CODIGO_PUBLICACAO INTEGER,
  CODIGO_CONSULTA INTEGER,
  CODIGO_AUDIENCIA INTEGER,
  CODIGO_AGENDAMENTO INTEGER,
  USUARIO_CADASTRO VARCHAR(15),
  USUARIO_ALTERACAO VARCHAR(15),
  DATA_ALTERACAO DATE,
  HORA_ALTERACAO VARCHAR(5),
  OBSERVACAO VARCHAR(8100),
  NOME_IMAGEM_ANDAMENTO VARCHAR(50),
  NUMERO_PROCESSO_AUX VARCHAR(30) NOT NULL,
  ATO_FATURAVEL VARCHAR(1));
ALTER TABLE ANDAMENTOS_PROCESSUAIS ADD CONSTRAINT PK_ANDAMENTOS_PROCESSUAIS PRIMARY KEY (CODIGO_ANDAMENTO);

ALTER TABLE ANDAMENTOS_PROCESSUAIS ADD CONSTRAINT FK_AND_NUMERO_PROCESSO FOREIGN KEY (NUMERO_PROCESSO) REFERENCES PROCESSOS(NUMERO_PROCESSO);

CREATE TABLE PROCESSOS (
  NUMERO_PROCESSO VARCHAR(30) NOT NULL,
  FASE_PROCESSO VARCHAR(25) NOT NULL,
  NUMERO_PROCESSO2 VARCHAR(30),
  FASE_PROCESSO2 VARCHAR(25),
  NUMERO_PROCESSO3 VARCHAR(30),
  FASE_PROCESSO3 VARCHAR(25),
  NUMERO_PROCESSO4 VARCHAR(30),
  FASE_PROCESSO4 VARCHAR(25),
  NUMERO_E_VARA4 VARCHAR(25),
  NUMERO_PROCESSO5 VARCHAR(30),
  FASE_PROCESSO5 VARCHAR(25),
  NUMERO_PROCESSO6 VARCHAR(30),
  FASE_PROCESSO6 VARCHAR(25),
  NUMERO_PROCESSO7 VARCHAR(30),
  FASE_PROCESSO7 VARCHAR(25),
  NUMERO_PROCESSO8 VARCHAR(30),
  FASE_PROCESSO8 VARCHAR(25),
  OBSERVACAO VARCHAR(2000));

put the complete code with the Unions and the table structure, if possible mount a sqlfiddle

– Rovann Linhalis

2017/07/13 at 19:27
The Code is now complete and the table structure is not in the comet

– Edu Mendonça

2017/07/13 at 20:29
I don’t know if this is Firedac error or Firebird thing test SQL in DBMS and when I step one by one it doesn’t duplicate anymore if I ask to go to the last record once it duplicates.

– Edu Mendonça

2017/07/13 at 20:32
You can edit the question by placing the structure. Have you tried to do without these Union All ?

– Rovann Linhalis

2017/07/13 at 20:51
I’m not as experienced in SQL as I would be without the union All?

– Edu Mendonça

2017/07/13 at 20:55
With only union , union all brings all the records, even if duplicated, only the Union does not

– Rovann Linhalis

2017/07/13 at 20:57
I’ll try to do it and see how it goes

– Edu Mendonça

2017/07/13 at 21:11
This querie will have to generate several performance problems, I believe it has to be more streamlined, you could change your question saying which records would like to search? In addition, the use of the * joker that serves to search for all attributes of the table is not advisable because it slows the querie excessively since it forces the DBMS to select all attributes of each table consulted several times. Depending on the type of attributes if it is a varchar for example can further worsen the execution times.

– Rafael Salomão

2017/07/13 at 21:12
more in query already says which records need to search... because I will pass all process records with the Archived phase to History table

– Edu Mendonça

2017/07/13 at 21:15
It says yes, but it doesn’t explain what these T2.NUMERO_PROCESSO7 fields are.What are you looking for?

– Rafael Salomão

2017/07/13 at 21:16
Under conditions like this one does not execute such a querie at runtime, on the contrary a view is created or an event that executes a stored and stores in a table the result of the execution. I’m just pointing out that in the future when the table gets records it won’t give you a headache.

– Rafael Salomão

2017/07/13 at 21:19
these fields are process that are linked to the Main process they may or may not have turnings and all turnings are shown from the Main.

– Edu Mendonça

2017/07/13 at 21:20
as I said above I am not experienced in SQL...

– Edu Mendonça

2017/07/13 at 21:22
Do not use UNION you have the foreign key between the tables you are wishing to rescue the process and all its correct tempos ? you can assemble all this with left joins without Union.

– Rafael Salomão

2017/07/13 at 21:24
I want to rescue all the movements being the main and the 7 if there are.

– Edu Mendonça

2017/07/13 at 21:29
Let’s go continue this discussion in chat.

– Rafael Salomão

2017/07/13 at 21:31

Show 11 more comments

1 answer

Browser other questions tagged sql firebird

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

by Rafael Salomão • **2,027** points · Answer 1 · 2017-07-13T21:58:00+00:00

I believe I can do that:

SELECT 
  TABNOV.CODIGO_ANDAMENTO,
  TABNOV.NUMERO_PROCESSO,
  TABNOV.DESCRICAO_ANDAMENTO,
  TABNOV.DATA,
  TABNOV.DATA_INCLUSAO,
  TABNOV.HORA,
  TABNOV.EMAIL_ENVIADO,
  TABNOV.CODIGO_PUBLICACAO,
  TABNOV.HORA_ALTERACAO,
  TABNOV.NOME_IMAGEM_ANDAMENTO,
  TABNOV.NUMERO_PROCESSO_AUX,
  TABNOV.ATO_FATURAVEL
FROM PROCESSOS T1 
LEFT JOIN ANDAMENTOS_PROCESSUAIS T2 
ON T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO
AND (T1.NUMERO_PROCESSO = T2.NUMERO_PROCESSO_AUX )
AND   (T1.FASE_PROCESSO LIKE "ARQUIVADO")

LEFT JOIN ANDAMENTOS_PROCESSUAIS T3                                        
T2 ON T1.NUMERO_PROCESSO = T3.NUMERO_PROCESSO 
AND T1.NUMERO_PROCESSO2 = T3.NUMERO_PROCESSO_AUX)               
AND  (T1.FASE_PROCESSO2 LIKE "ARQUIVADO")

You could do several left Join like this by repeating and filtering the results by the fields you want. The QUERY above takes all records from the table processes and their relationships if they exist with the table procedural movements.

I’m not sure it would work. I would need the tables with the data to do performance tests and if it works properly to what you want and I don’t have full understanding of what you want to do. But it is an attempt to eliminate all these UNIONS consume an exaggeration of CPU from your machine and generate temporary tables that consume a lot of memory. If there are number of users running this same querie at the same time could consume the entire resource of your server.