Forum Stats

  • 3,770,131 Users
  • 2,253,073 Discussions
  • 7,875,337 Comments

Discussions

I keep getting duplicate values in my query, how can I delete them or eliminate?

User_5111K
User_5111K Member Posts: 5 Green Ribbon

SELECT

dst.organization_code AS destination_organization,

-- Datos del pago

TO_CHAR(pmt.payment_date, 'DD/MM/YYYY') AS payment_date,

chk.check_number AS payment_number,

chk.status_lookup_code,

pmt.payment_status,

pmt.INT_BANK_NAME,

pmt.payment_method_code,

pmt.payment_reference_number,

pmt.paper_document_number,

pmt.payment_currency_code,

pmt.payment_amount,

-- Beneficiario

pmt.payee_name,

-- Facturas pagadas

inv.invoice_num,

TO_CHAR(inv.invoice_date, 'DD/MM/YYYY') AS invoice_date,

inv.invoice_currency_code,

inv.invoice_amount,

inv.amount_applicable_to_discount,

inv.amount_paid AS invoice_amount_paid,

invpmt.payment_num AS sequence_payment_num,

invpmt.amount AS invoice_payment_amount,

-- OC

pha.segment1 as ORDEN_DE_COMPRA

FROM

IBY_PAYMENTS_ALL pmt,

AP_CHECKS_ALL chk,

AP_INVOICE_PAYMENTS_ALL invpmt,

AP_INVOICES_ALL inv,

AP_INVOICE_LINES_ALL invl,

PO_LINE_LOCATIONS_ALL poll,

PO_HEADERS_ALL pha,

INV_ORG_PARAMETERS dst

WHERE chk.payment_id = pmt.payment_id

AND invpmt.check_id = chk.check_id

AND inv.invoice_id = invpmt.invoice_id

--AND pmt.payment_date >= TRUNC(SYSDATE)-5

AND pmt.payment_amount > 1

AND pmt.payment_status = 'ISSUED'

AND pmt.payment_date >= :P_DATE_FROM

AND pmt.payment_date <= :P_DATE_TO

-----

AND invl.invoice_id = inv.invoice_id

AND poll.line_location_id = invl.po_line_location_id

AND pha.po_header_id = poll.po_header_id

AND dst.organization_id = poll.ship_to_organization_id

AND poll.ship_to_organization_id = NVL(:P_ORGANIZATION_ID, poll.ship_to_organization_id)




GROUP BY

dst.organization_code,

pmt.payment_date,

chk.check_number,

chk.status_lookup_code,

pmt.payment_status,

pmt.INT_BANK_NAME,

pmt.payment_method_code,

pmt.payment_reference_number,

pmt.paper_document_number,

pmt.payment_currency_code,

pmt.payment_amount,

pmt.payee_name,

inv.invoice_num,

INV.INVOICE_DATE,

inv.invoice_currency_code,

inv.invoice_amount,

inv.amount_applicable_to_discount,

inv.amount_paid,

invpmt.payment_num,

invpmt.amount,

pha.segment1


UNION ALL

-------------------------------------------------------------------------------------------------------

SELECT

NULL AS destination_organization,

-- Datos del pago

TO_CHAR(pmt.payment_date, 'DD/MM/YYYY') AS payment_date,

chk.check_number AS payment_number,

chk.status_lookup_code,

pmt.payment_status,

pmt.INT_BANK_NAME,

pmt.payment_method_code,

pmt.payment_reference_number,

pmt.paper_document_number,

pmt.payment_currency_code,

pmt.payment_amount,

-- Beneficiario

pmt.payee_name,

-- Facturas pagadas

inv.invoice_num,

TO_CHAR(inv.invoice_date, 'DD/MM/YYYY') AS invoice_date,

inv.invoice_currency_code,

inv.invoice_amount,

inv.amount_applicable_to_discount,

inv.amount_paid AS invoice_amount_paid,

invpmt.payment_num AS sequence_payment_num,

invpmt.amount AS invoice_payment_amount,

-- OC

NULL AS ORDEN_DE_COMPRA


FROM

IBY_PAYMENTS_ALL pmt,

AP_CHECKS_ALL chk,

AP_INVOICE_PAYMENTS_ALL invpmt,

AP_INVOICES_ALL inv

WHERE chk.payment_id = pmt.payment_id

AND invpmt.check_id = chk.check_id

AND inv.invoice_id = invpmt.invoice_id

--AND pmt.payment_date >= TRUNC(SYSDATE)-5

AND pmt.payment_amount > 1

AND pmt.payment_status = 'ISSUED'

AND pmt.payment_date >= :P_DATE_FROM

AND pmt.payment_date <= :P_DATE_TO

-----

AND inv.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'

GROUP BY

pmt.payment_date,

chk.check_number,

chk.status_lookup_code,

pmt.payment_status,

pmt.INT_BANK_NAME,

pmt.payment_method_code,

pmt.payment_reference_number,

pmt.paper_document_number,

pmt.payment_currency_code,

pmt.payment_amount,

pmt.payee_name,

inv.invoice_num,

INV.INVOICE_DATE,

inv.invoice_currency_code,

inv.invoice_amount,

inv.amount_applicable_to_discount,

inv.amount_paid,

invpmt.payment_num,

invpmt.amount


UNION ALL

------------------------------------------------------------------------------------------------------

SELECT

NULL AS destination_organization,

-- Datos del pago

TO_CHAR(pmt.payment_date, 'DD/MM/YYYY') AS payment_date,

chk.check_number AS payment_number,

chk.status_lookup_code,

pmt.payment_status,

pmt.INT_BANK_NAME,

pmt.payment_method_code,

pmt.payment_reference_number,

pmt.paper_document_number,

pmt.payment_currency_code,

pmt.payment_amount,

-- Beneficiario

pmt.payee_name,

-- Facturas pagadas

min (inv.invoice_num),

TO_CHAR(inv.invoice_date, 'DD/MM/YYYY') AS invoice_date,

inv.invoice_currency_code,

inv.invoice_amount,

inv.amount_applicable_to_discount,

inv.amount_paid AS invoice_amount_paid,

invpmt.payment_num AS sequence_payment_num,

invpmt.amount AS invoice_payment_amount,

-- OC

NULL AS ORDEN_DE_COMPRA


FROM

IBY_PAYMENTS_ALL pmt,

AP_CHECKS_ALL chk,

AP_INVOICE_PAYMENTS_ALL invpmt,

AP_INVOICE_LINES_ALL invl,

AP_INVOICES_ALL inv

WHERE chk.payment_id = pmt.payment_id

AND invpmt.check_id = chk.check_id

AND inv.invoice_id = invpmt.invoice_id

--AND pmt.payment_date >= TRUNC(SYSDATE)-5

AND pmt.payment_amount > 1

AND pmt.payment_status = 'ISSUED'

AND pmt.payment_date >= :P_DATE_FROM

AND pmt.payment_date <= :P_DATE_TO

-----

AND inv.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'

AND invl.line_type_lookup_code='ITEM'


AND invl.po_line_location_id IS NULL


--AND chk.check_number = '210408124005'

--AND chk.check_number = '210408125102'

GROUP BY

pmt.payment_date,

chk.check_number,

chk.status_lookup_code,

pmt.payment_status,

pmt.INT_BANK_NAME,

pmt.payment_method_code,

pmt.payment_reference_number,

pmt.paper_document_number,

pmt.payment_currency_code,

pmt.payment_amount,

pmt.payee_name,

inv.invoice_num,

INV.INVOICE_DATE,

inv.invoice_currency_code,

inv.invoice_amount,

inv.amount_applicable_to_discount,

inv.amount_paid,

invpmt.payment_num,

invpmt.amount



ORDER BY

destination_organization,

payee_name

Tagged:

Best Answer

  • James Su
    James Su Member Posts: 1,114 Gold Trophy
    Accepted Answer

    You have a big column list in "group by" however there's no aggregation ( I only see min (inv.invoice_num) which doesn't make sense at all).

    This means there's something wrong with your joins. Please check all join conditions carefully. Usually there should be a unique key on one side of the join.

    For example, in the last section of "union all", table "invl" is not being used in the join, which is probably a bug.

    User_5111K

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @User_5111K

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

    Simplify the problem, if possible. Post just enough to show what the problem is. In this case, you might only need two tables with maybe four columns each.

    Perhaps you should be doing a GROUP BY after the UNION. (You might not need the GROUP BYs in each branch of the UNION.)

  • User_5111K
    User_5111K Member Posts: 5 Green Ribbon

    In excel format is ok?

    If you look at the two rows are identical except two columns, that should exclude them with this clause

    AND invl.po_line_location_id IS NULL

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond

    No-one on this forum will likely open an excel or word document - a common source of viral infections.

    Please include create table scripts and insert scripts in text, inline in the thread.

  • User_5111K
    User_5111K Member Posts: 5 Green Ribbon

    DATA_DS

    P_DATE_FROM (2021-04-08T18:00:00.000-06:00)

    P_DATE_TO (2021-04-08T18:00:00.000-06:00)

    P_ORGANIZATION_ID (300000006146893)

    G_1

    DESTINATION_ORGANIZATION (FILOA_AUTO_CEDISMT)

    PAYMENT_DATE (09/04/2021)

    PAYMENT_NUMBER (210409192105)

    STATUS_LOOKUP_CODE (CLEARED)

    PAYMENT_STATUS (ISSUED)

    PAYMENT_METHOD_CODE (Electronico)

    PAYMENT_REFERENCE_NUMBER (4867)

    PAPER_DOCUMENT_NUMBER (210409192105)

    PAYMENT_CURRENCY_CODE (MXN)

    PAYMENT_AMOUNT (19928.84)

    PAYEE_NAME (SUKARNE SA DE CV)

    INVOICE_CURRENCY_CODE (MXN)

    INVOICE_AMOUNT (10968.82)

    AMOUNT_APPLICABLE_TO_DISCOUNT (10968.82)

    INVOICE_AMOUNT_PAID (10968.82)

    SEQUENCE_PAYMENT_NUM (1)

    INVOICE_PAYMENT_AMOUNT (10968.82)

    ORDEN_DE_COMPRA (OC12702)

    INT_BANK_NAME (Banco Santander (México), S.A., Institución de Banca Múltiple)

    INVOICE_DATE (24/03/2021)

    INVOICE_NUM (CCA-1331659) SAME INVOICE_NUM


    G_1

    PAYMENT_DATE (09/04/2021)

    PAYMENT_NUMBER (210409192105)

    STATUS_LOOKUP_CODE (CLEARED)

    PAYMENT_STATUS (ISSUED)

    PAYMENT_METHOD_CODE (Electronico)

    PAYMENT_REFERENCE_NUMBER (4867)

    PAPER_DOCUMENT_NUMBER (210409192105)

    PAYMENT_CURRENCY_CODE (MXN)

    PAYMENT_AMOUNT (19928.84)

    PAYEE_NAME (SUKARNE SA DE CV)

    INVOICE_CURRENCY_CODE (MXN)

    INVOICE_AMOUNT (10968.82)

    AMOUNT_APPLICABLE_TO_DISCOUNT (10968.82)

    INVOICE_AMOUNT_PAID (10968.82)

    SEQUENCE_PAYMENT_NUM (1)

    INVOICE_PAYMENT_AMOUNT (10968.82)

    INT_BANK_NAME (Banco Santander (México), S.A., Institución de Banca Múltiple)

    INVOICE_DATE (24/03/2021)

    INVOICE_NUM (CCA-1331659)

  • James Su
    James Su Member Posts: 1,114 Gold Trophy
    Accepted Answer

    You have a big column list in "group by" however there's no aggregation ( I only see min (inv.invoice_num) which doesn't make sense at all).

    This means there's something wrong with your joins. Please check all join conditions carefully. Usually there should be a unique key on one side of the join.

    For example, in the last section of "union all", table "invl" is not being used in the join, which is probably a bug.

    User_5111K
  • User_5111K
    User_5111K Member Posts: 5 Green Ribbon

    Thank you James Su you where right..this solved mi issue

  • user2660760
    user2660760 Member Posts: 1 Green Ribbon

    there are 2 possible solutions for your duplicate lines query:

    1. you should include in the SELECT statement, the invoice line number id
    2. you shoud remove from the WHERE & FROM statements all invoice lines joins