Ignore date / time field in group by

Question

Ignore date / time field in group by

Navigation

#1 by (1 votes)

1

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

It returns me to the situation below:

Youwouldneedtodisplaythesumofthequantitiesofthesameiteminsomeway,regardlessofthedate.IfItakethetimefromthePCN2.datafielditworksnormally,asitshould.

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

sql oracle

asked by anonymous 13.09.2016 / 14:00

1 answer

How to create a text file on Android? Crashing when 2nd activity starts

score 1 · Accepted Answer

To register. I solved by creating a subquery, taking the last date (max). That way, he got what he 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