decode, grouping in sql question

[veronic]

I have the following SQL:

select
decode(grouping(r.status_desc)+grouping(r.status_code)+grouping(r.AUDIT_DEPT_ID),0, r.status_CODE, 'Total') status_code,
decode(grouping(r.status_code)+grouping(r.STATUS_DESC)+grouping(r.AUDIT_DEPT_ID),0, r.AUDIT_DEPT_ID, NULL) AUDIT_DEPT_ID,
decode(grouping(r.status_code)+grouping(r.status_desc)+grouping(r.AUDIT_DEPT_ID),0, r.status_desc , 'Total') status_desc,
sum(balance_due) as Balance,
sum(amount_due) as Amount,
count(project_number) as Cases,
sum(CUMULATIVE_ADJUSTMENTS) as cumulative_adjustments
FROM facts2_reports.v_reports_balance r, facts2.v_status_grouping_xref x
where balance_due <> 0 and
r.STATUS_CODE_ID = x.STATUS_CODE_ID(+) AND
r.AUDIT_DEPT_ID = ('P')
group by rollup(r.status_code,r.status_desc,r.AUDIT_DEPT_ID)
having grouping(r.status_code)+grouping(r.status_desc)+grouping(r.audit_dept_id) in (0,3)

And I receive:

02 P Available for Audit 500,000.00
07 P Final Report Issued 15,140,453.40
08 P Audit Complete - Collection in progress
Total I have status_ code (first column)and 'P' - audit dep in second column everywhere but last row "Total". I need to have Audit dep for 'Total'. How can I do this???
Thank you

 

[dnoeth]

You filter audit dep within the decode, so just remove that.

But why do you use ROLLUP and then filter for unwanted groups instead of a simple:
group by
grouping sets((r.status_code,r.status_desc,r.AUDIT_DEPT_ID)
,())


Btw,
this looks like Oracle code, i'd recommend replacing DECODE with CASE and Oracle style Outer Join (+) with ANSI sytle LEFT OUTER JOIN.