Ignore group date/time field by

Question

I have the following query:

Select
                    msi.segment1       codigo,
                    msi.description    descricao,
                    pcn1.quantidade    Qtde_Romaneio,
                    nvl(SUM(pcn2.quantidade),0)    Qtde_Coleta,
                    (nvl(sum(pcn2.quantidade),0) - pcn1.quantidade) Diferenca,
                    nvl(pcn2.ip,0)            Coletor,
                    nvl(PCN2.USUARIO,0)       Usuario,
                    nvl(pcn2.reconferencia,'X') Reconferencia,
                    to_char(PCN2.data, 'DD/mm/yyyy hh24:mi:ss')  Data
                    from
                    PCN_ROMANEIO_RESUMO    pcn1,
                    PCN_ROMANEIO_COLETA    pcn2,
                    MTL_SYSTEM_ITEMS       msi,
                    MTL_CROSS_REFERENCES_V mrv
                    Where
                    pcn1.codigo           = mrv.cross_reference
                    and msi.inventory_item_id = mrv.inventory_item_id(+)
                    and msi.organization_id   = 104
                    and mrv.CROSS_REFERENCE_TYPE(+) = 'DUN14'
                    and pcn1.codigo           = pcn2.codigo(+)
                    and pcn1.romaneio         = pcn2.romaneio(+)
                    and pcn1.romaneio         = ('$romaneio')
                    group by
                    msi.segment1,
                    msi.description,
                    nvl(pcn2.ip,0),
                    PCN2.USUARIO,
                    nvl(pcn2.reconferencia,'X'),
                    pcn1.quantidade,
                    to_char(PCN2.data, 'DD/mm/yyyy hh24:mi:ss')
                    order by 1

The same returns me to the situation below:

It would need to somehow display the sum of the quantities of the same item, without considering the date. If I take the time from the field PCN2.data it works normally, as it should.

Exists as "ignore" the field PCN2.data at the time of group by, but display it in the query?

Answer 1

For record. I decided to create a subquery, taking the last date (max). This way, met what I needed:

Select
          msi.segment1       codigo,
          msi.description    descricao,
          pcn1.quantidade    Qtde_Romaneio,
          nvl(SUM(pcn2.quantidade),0)    Qtde_Coleta,
          (nvl(sum(pcn2.quantidade),0) - pcn1.quantidade) Diferenca,
          nvl(pcn2.ip,0)            Coletor,
          nvl(PCN2.USUARIO,0)       Usuario,
          nvl(pcn2.reconferencia,'X') Reconferencia,
          PCN_Data.data Data
          from
          PCN_ROMANEIO_RESUMO    pcn1,
          PCN_ROMANEIO_COLETA    pcn2,
          MTL_SYSTEM_ITEMS       msi,
          MTL_CROSS_REFERENCES_V mrv,
          (Select
           msi.segment1       codigo,
           to_char(max(PCN2.data), 'DD/mm/yyyy hh24:mi:ss')  Data
           from
           PCN_ROMANEIO_RESUMO    pcn1,
           PCN_ROMANEIO_COLETA    pcn2,
           MTL_SYSTEM_ITEMS       msi,
           MTL_CROSS_REFERENCES_V mrv                    
           Where
           pcn1.codigo           = mrv.cross_reference
           and msi.inventory_item_id = mrv.inventory_item_id(+)
           and msi.organization_id   = 104
           and mrv.CROSS_REFERENCE_TYPE(+) = 'DUN14'
           and pcn1.codigo           = pcn2.codigo(+)
           and pcn1.romaneio         = pcn2.romaneio(+)
           and pcn1.romaneio         = ('$romaneio')
           group by
           msi.segment1
           order by 1) PCN_Data
          Where
          pcn1.codigo           = mrv.cross_reference
          and PCN_Data.codigo   = msi.segment1
          and msi.inventory_item_id = mrv.inventory_item_id(+)
          and msi.organization_id   = 104
          and mrv.CROSS_REFERENCE_TYPE(+) = 'DUN14'
          and pcn1.codigo           = pcn2.codigo(+)
          and pcn1.romaneio         = pcn2.romaneio(+)
          and pcn1.romaneio         = ('$romaneio')
          group by
          msi.segment1,
          msi.description,
          nvl(pcn2.ip,0),
          PCN2.USUARIO,
          nvl(pcn2.reconferencia,'X'),
          pcn1.quantidade,
          PCN_Data.Data
          order by 1