Solved

grouping duplicate* rows in SQL

Posted on 2014-09-05
2
414 Views
Last Modified: 2014-09-09
Can anyone assist with some SQL syntax, I have a base query (see below). It basically links to invoices in our ERP application. We have an apa.amount_paid column, but the issue is it appears 1 or many times based on the number of lines in the invoice. Lines represent various elements of the invoice, and subsequently the amount paid column is appearing numerous times based on the number of lines. So if an invoice had 4 lines, I get 4 rows giving me the same amount paid information i.e. £456.77.
I need a way to crunch the results to only show one row per invoice. Can anyone give any pointers. Each invoice can be grouped either by apa.invoice_id or apa.invoice_num. You will have to excuse my knowledge in this area, very new to it and the SQL DBA is off long time sick so trying to tweak certain queries to get what I need. I fear a group by won’t give me what I need either with the columns currently in the select, and a group by with SUM on the apa.amount_paid won’t work either and would give inaccurate data and total the same apa.amount_paid  amount several times over (i.e if 4 lines 4x the apa.amount_paid).
The troublesome rows are aida.LINE_TYPE_LOOKUP_CODE, aida.distribution_line_number, which is where it breaks the invoice down into several rows. Any pointers?

SELECT
apa.invoice_num,
to_char(apa.amount_paid,'9999999.99') as amount_paid,
apa.invoice_date, aida.accounting_date,
apa.doc_sequence_value as "VOUCHER_NUMBER",
aida.invoice_ID, to_char(aida.amount,'9999999.99') as amount,
aida.LINE_TYPE_LOOKUP_CODE, aida.distribution_line_number,
gcc.code_combination_ID,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
apa.invoice_id,
apa.ACCTS_PAY_CODE_COMBINATION_ID,
apa.vendor_ID,
ass.vendor_ID,
ass.vendor_name

FROM
apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc,
apps.ap_invoices_all apa,
apps.ap_suppliers ass

WHERE
aida.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND aida.invoice_ID = apa.invoice_ID
AND apa.vendor_ID = ass.vendor_ID
AND gcc.segment2 IN (‘123’,’456’,’789’,’000’)
--AND aida.line_type_lookup_code = 'ITEM'
AND apa.amount_paid > 5000
--AND ass. vendor_type_lookup_code = 'ONE'
order by apa.doc_sequence_value
0
Comment
Question by:pma111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 40305886
Can you post some sample data (just the relevant portion no need for every column) that demonstrates your problem and what you would like to see as a result? Please do it in a text format and not as a picture.
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 40306098
What did you expect when you are selecting each "line" as in:

  SELECT Apa.Invoice_Num
       , TO_CHAR ( Apa.Amount_Paid, '9999999.99' ) AS Amount_Paid
       , Apa.Invoice_Date
       , Aida.Accounting_Date
       , Apa.Doc_Sequence_Value AS "VOUCHER_NUMBER"
      , Aida.Invoice_Id
       , TO_CHAR ( Aida.Amount, '9999999.99' ) AS Amount
       , Aida.Line_Type_Lookup_Code
       , Aida.Distribution_Line_Number
      , Gcc.. .
       . . .   E t c  
    FROM Apps.Ap_Invoice_Distributions_All Aida
       , Apps.Gl_Code_Combinations Gcc
       , Apps.Ap_Invoices_All Apa
       , Apps.Ap_Suppliers Ass
   WHERE Aida.Dist_Code_Combination_Id = . . . E t c

If you want one line per invoice, then you need to use an aggregate function like MAX() or SUM() or COUNT() on these columns and group by the other columns, something like this:
  SELECT Apa.Invoice_Num
       , TO_CHAR ( Apa.Amount_Paid, '9999999.99' ) AS Amount_Paid
       , Apa.Invoice_Date
       , MAX ( Aida.Accounting_Date ) AS Aida_Accounting_Date
       , Apa.Doc_Sequence_Value AS "VOUCHER_NUMBER"
       , MAX ( Aida.Invoice_Id ) AS Aida_Invoice_Id
       , TO_CHAR ( SUM ( Aida.Amount ), '9999999.99' ) AS Amount
       , LISTAGG ( Aida.Line_Type_Lookup_Code, ', ' )
            WITHIN GROUP (ORDER BY Aida.Distribution_Line_Number)
            AS Aida_Line_Type_Lookup_Codes
       , COUNT ( Aida.Distribution_Line_Number ) AS Aida_Distribution_Lines
       , Gcc.Code_Combination_Id
       , Gcc.Segment1
       , Gcc.Segment2
       , Gcc.Segment3
       , Gcc.Segment4
       , Apa.Invoice_Id
       , Apa.Accts_Pay_Code_Combination_Id
       , Apa.Vendor_Id
       , Ass.Vendor_Id
       , Ass.Vendor_Name
    FROM Apps.Ap_Invoice_Distributions_All Aida
       , Apps.Gl_Code_Combinations Gcc
       , Apps.Ap_Invoices_All Apa
       , Apps.Ap_Suppliers Ass
   WHERE Aida.Dist_Code_Combination_Id = Gcc.Code_Combination_Id
     AND Aida.Invoice_Id = Apa.Invoice_Id
     AND Apa.Vendor_Id = Ass.Vendor_Id
     AND Gcc.Segment2 IN ('123', '456', '789', '000')
     AND Apa.Amount_Paid > 5000
GROUP BY Apa.Invoice_Num
       , TO_CHAR ( Apa.Amount_Paid, '9999999.99' )
       , Apa.Invoice_Date
       , Apa.Doc_Sequence_Value
       , Gcc.Code_Combination_Id
       , Gcc.Segment1
       , Gcc.Segment2
       , Gcc.Segment3
       , Gcc.Segment4
       , Apa.Invoice_Id
       , Apa.Accts_Pay_Code_Combination_Id
       , Apa.Vendor_Id
       , Ass.Vendor_Id
       , Ass.Vendor_Name
ORDER BY Apa.Doc_Sequence_Value;

Open in new window

:p
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question