ORACLE SQL … I am trying to combine rows into one when they share the same contents in the following fields:
x
ENTITY_UNIQUE_IDENTIFIER
ACCOUNT_NUMBER
AB_CODE_B
ACCOUNT_DESCRIPTION
ACCOUNT_TYPE
**AND** when FINANCIAL_STATEMENT = 'A'
In addition, I need the sum of the fields GL_ACCOUNT_BALANCE
from all the respective previous rows to be totaled onto that one new row.
I have written the following code.
Select ab.company ENTITY_UNIQUE_IDENTIFIER,ab.account ACCOUNT_NUMBER,
CASE
When Substr(ab.account,1,1) in ('1','2','3') or Substr(ab.account,1,4) = '9999' Then ''
When Substr(ab.account,1,1) NOT in ('1','2','3') or Substr(ab.account,1,4) != '9999' Then ab.CODE_B
End ab_CODE_B,
CASE
When Substr(ab.account,1,1) in ('1','2','3') or Substr(ab.account,1,4) = '9999' Then ''
When Substr(ab.account,1,1) NOT in ('1','2','3') or Substr(ab.account,1,4) != '9999' Then ab.CODE_B_DESC
End ab_CODE_B,
' ' KEY_5,
' ' KEY_6,
' ' KEY_7,
' ' KEY_8,
' ' KEY_9,
' ' KEY_10,
a.description ACCOUNT_DESCRIPTION,
' ' ACCOUNT_REFERENCE,
Case
When Substr(ab.account,1,1) in ('1','2','3') or Substr(ab.account,1,4) = '9999' Then 'A'
Else 'I'
End FINANCIAL_STATEMENT,----
Case
When (ab.account_type = 'ASSETS' or ab.account_type = 'ASSETS1' or ab.account_type = 'ASSETS2'
or ab.account_type = 'ASSETS3') Then 'Asset'
When ab.account_type = 'LIABILITIES' or ab.account_type = 'LIABILITIES1' Then 'Liability'
When ab.account_type = 'EXPENSE' or ab.account_type = 'EXPENSE1' or ab.account_type = 'EXPENSE2'
Or ab.account_type = 'COGS' Then 'Expense'
When ab.account_type = 'REVENUES' Then 'Revenue'
When ab.account_type = 'EQUITY' Then 'Equity'
Else ab.account_type
End ACCOUNT_TYPE,
'TRUE' ACTIVE_ACCOUNT,
Case
When Sum(ab.debet_balance) + Sum(ab.credit_balance) = 0 Then 'FALSE'
Else 'TRUE'
End ACTIVITY_IN_PERIOD,
' ' ALTERNATE_CURRENCY,
Case
When ab.company = 'ZZZZ' Then 'USD'
When ab.company = 'SSSS' Then 'CAD'
When ab.company = 'wwww' Then 'EUR'
When ab.company = 'wwwwE' Then 'USD'
When ab.company = 'QWQW' Then 'CNY'
End ACCOUNT_CURRENCY,
To_Char(Trunc(Last_Day(Sysdate - 10)),'mm/dd/yyyy') PERIOD_END_DATE,
' ' GL_REPORTING_BALANCE,
Sum(ab.amount_balance) GL_ACCOUNT_BALANCE,
' ' ACCOUNT_REFERENCE2,
' ' ACCOUNT_REFERENCE3,
' ' ACCOUNT_REFERENCE4,
' ' ACCOUNT_REFERENCE5,
' ' ACCOUNT_REFERENCE6
From account a
Left Outer Join accounting_balance_budget ab
On ab.account = a.account
And ab.company = a.company
Where ab.accounting_year = Extract(year from Sysdate - 10)
And ab.accounting_period <= Extract(Month from Sysdate - 10)
And Sysdate between a.valid_from and a.valid_until
And ab.company in ('ZZZZ','SSSS','wwww','wwwwE','QWQW')
And (Substr(ab.account,1,1) in ('1','2','3','4','5','6','7','8') or Substr(ab.account,1,4) = '9999')
Group by ab.company,ab.account,ab.company, ab.account_type, ab.accounting_year,ab.code_b,ab.CODE_B_DESC, a.accnt_group, a.description
HAVING Sum(ab.amount_balance) >0
Order by 2, 1;
Advertisement
Answer
From my point of view, code you posted is too complex. You should have posted something simpler.
Anyway: if I understood you correctly, you’re looking for LISTAGG
function which “aggregates” column values (lists them, one after another, in a single column), along with the SUM
function (to calculate balance summary).
Sample code:
SELECT entity_unique_identifier,
account_number,
ab_code_b,
account_description,
account_type,
--
LISTAGG (some_other_column, ', ') WITHIN GROUP (ORDER BY NULL) some_other_column,
SUM (gl_account_balance) sum_gl_account_balance
FROM
WHERE
GROUP BY entity_unique_identifier,
account_number,
ab_code_b,
account_description,
account_type;
As you can see, columns that aren’t aggregated have to be listed in the GROUP BY
clause.
A simple example, based on Scott’s EMP
table:
SQL> select deptno,
2 job,
3 listagg(ename, ', ') within group (order by null) enames,
4 sum(sal) sum_salary
5 from emp
6 group by deptno,
7 job;
DEPTNO JOB ENAMES SUM_SALARY
---------- --------- ---------------------------------------- ----------
10 CLERK MILLER 1300
10 MANAGER CLARK 2450
10 PRESIDENT KING 5000
20 CLERK ADAMS, SMITH 1900
20 ANALYST FORD, SCOTT 6000
20 MANAGER JONES 2975
30 CLERK JAMES 950
30 MANAGER BLAKE 2850
30 SALESMAN ALLEN, MARTIN, TURNER, WARD 5600
9 rows selected.
SQL>