Select with duplicate record

Asked

Viewed 1,181 times

2

I have a question that may be trivial for most, but that I stuck here, I seek to research a lot before posting, but I will need a help.

I am setting up a select to bring me the closure of the results with base date, for this I need, for example, take as a basis 31/12/2015, in the structure of the database the tables that will be consulted docum and docum_pgto, the table docum contains the documents generated and the docum_pgto records payment events (1 or more) for each document.

It turns out that when one docum has more than one payment on the table docum_pgto, the records are being duplicated, the result of the amount of the balance that I am calculating is correct, because it pulls the amount of the payment, but for me to total the gross, I have these duplicated events that distort the result.

 select s1.cod_empresa,
       s1.num_docum,
       s1.ies_tip_docum,
       s1.dat_emis,
       s1.dat_vencto_s_desc,
       --s1.dat_pgto,
       s1.cod_cliente,
       s1.nom_cliente,
       s1.num_cgc_cpf,
       s1.cod_portador,
       s1.ies_pgto_docum,
       s1.val_bruto,
       s1.val_liquido,
       case when sum(val_pago) is null then 0 else sum(val_pago) end val_pago,
       s1.val_saldo,
       case when (s1.dat_pgto>='01/01/2015') then sum(s1.val_pago) + sum(s1.val_saldo) else s1.val_saldo end val_saldo2
       from (

select distinct 
       d.cod_empresa,
       d.num_docum,
       d.ies_tip_docum,
       d.dat_emis,
       d.dat_vencto_s_desc,
       dp.dat_pgto,
       d.cod_cliente,
       c.nom_cliente,
       c.num_cgc_cpf,
       d.cod_portador,
       d.ies_pgto_docum,
       d.val_bruto,
       d.val_liquido,
       dp.val_pago,
       d.val_saldo
    from docum d 
       left join clientes c on (c.cod_cliente=d.cod_cliente)
       left join docum_pgto dp on (dp.cod_empresa=d.cod_empresa) and (dp.num_docum=d.num_docum)
       where d.ies_tip_docum='NC'
       and d.dat_emis<='31/12/2014'
       and d.ies_situa_docum<>'C'
       and d.val_saldo>0
       and d.cod_empresa=01
    
union

select distinct 
       d.cod_empresa,
       d.num_docum,
       d.ies_tip_docum,
       d.dat_emis,
       d.dat_vencto_s_desc,
       dp.dat_pgto,
       d.cod_cliente,
       c.nom_cliente,
       c.num_cgc_cpf,
       d.cod_portador,
       d.ies_pgto_docum,
       d.val_bruto,
       d.val_liquido,
       dp.val_pago,
       d.val_saldo
 
    from docum d 
       left join clientes c on (c.cod_cliente=d.cod_cliente)
       left join docum_pgto dp on (dp.cod_empresa=d.cod_empresa) and (dp.num_docum=d.num_docum)
       where d.ies_tip_docum='NC'
       and d.dat_emis<='31/12/2014'
       and d.ies_situa_docum<>'C'
       and dp.dat_pgto>='01/01/2015'
       and d.cod_empresa=01

) s1
group by s1.cod_empresa,
       s1.num_docum,
       s1.ies_tip_docum,
       s1.dat_emis,
       s1.dat_vencto_s_desc,
       s1.cod_cliente,
       s1.nom_cliente,
       s1.num_cgc_cpf,
       s1.cod_portador,
       s1.ies_pgto_docum,
       s1.val_bruto,
       s1.val_liquido,
       s1.val_saldo,
       s1.dat_pgto

the return of this command is as follows:

1 10549 NC 24/07/2014 19/08/2015 EMPRESA NOME 313,74 313,74 278,40  278,40
1 10549 NC 24/07/2014 23/11/2015 EMPRESA NOME 313,74 313,74  35,34   35,34
1 10550 NC 24/07/2014 19/08/2015 EMPRESA NOME 650,90 650,90 163,38  163,38
1 10550 NC 24/07/2014 23/11/2015 EMPRESA NOME 650,90 650,90 487,52  487,52
1 10975 NC 03/10/2014 24/02/2015 EMPRESA NOME 1.350 1.350   135,00  135,00
1 10975 NC 03/10/2014 03/10/2014 EMPRESA NOME 1.350 1.350   1.215   215,00
1 11476 NC 08/12/2014 08/12/2014 EMPRESA NOME 1.200 1.200   240,00  240,00
1 11476 NC 08/12/2014 08/12/2014 EMPRESA NOME 1.200 1.200   960,00  960,00

... summarized the return of the above command ...

Then the records of documents 10549, 10550, 10975, 11476 repeat because they had more than one payment, the value of the last column that is the sum of the balance is ok because it sums the value of the pgto with the value of the balance, but the first and the second column that are the gross values, they are doubling.

I tried to organize the query to be easy to understand, I stayed practically 10 years relar the hand in SQL, it is complicated, gets rusty.. It may be a simple detail, but if anyone could give a help I would be very grateful.

Thanks

select pl-sql

  • Friend, unfortunately in the form that is the select, it will not be possible to return only in one line. As there are multiple payments for a single document, do you agree that these payments can be made on different dates and at different low values? For this reason, even giving group by in the columns, as there are differences in the values of the payment columns for the same document, the result will be returned in more than one row.

  • Solutions 1 value paid in a Function 2 value paid in a view or virtual table totaling the paid the problem would be the data_pgto work with the maximum maybe..

2 answers

0

By the design of your select I see no way to return the fields you want to return, you get the result you want.

You could perform a group by but that would imply not having the amount of returns you want.

You could use the distintc but would also have to omit some campuses in your selection.

You could also use rank, that would change the direction of your query, and remove duplicates.

But none of these solutions meet your need.

0

Change group by to : 

group by s1.cod_empresa,
       s1.num_docum

Do this and pass the feedback. I believe you solve.

Browser other questions tagged select pl-sql

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