This discussion is archived
2 Replies Latest reply: Sep 10, 2013 9:04 AM by Greg.Spall RSS

Error - SQL Report Total included as part of sort. How do I exclude total?

V Rickert Newbie
Currently Being Moderated

Hello.

I have a summary report that is including the Totals as part of the sort.  How do I get exclude the total from the sort?

Here is my code:

 

<code>

select nvl(to_char(vendor),'Totals:')

vendor,

po_number,

contract_number,

po_amount,

inv_total,

cast(round(case when po_amount = 0 then 0 else nvl(inv_total/po_amount,0) end, 2)*100 as varchar(10)) || '%' percent

from

(

select

vendor,

po_number,

contract_number,

sum(nvl(po_amount,0)) po_amount,

sum(nvl(inv_total,0)) inv_total

from

(

select b.vendor,

b.po_number,

b.wbs,

b.contract_number,

b.po_amount,

a.inv_total

from (

select

po_number,

wbs,

sum(inv_total)inv_total

from

invoice,invoice_details,project

where invoice.inv_id = invoice_details.inv_id

and substr(invoice_details.wbs,1,6) = project.wbs_sequence

and project.project_number = nvl(:F101_FPC_NUMBER,project.project_number)

group by po_number, wbs) a,

(

select vendor, po_number, wbs, contract_number, sum(po_amount)po_amount

from po, po_details, project

where po.po_id = po_details.po_id

and substr(po_details.wbs,1,6) = project.wbs_sequence

and project.project_number = nvl(:F101_FPC_NUMBER,project.project_number)

group by vendor, po_number, contract_number, wbs)b

where a.po_number(+)=b.po_number

and a.wbs(+) = b.wbs)

group by grouping sets((),(

vendor,

po_number,

wbs,

contract_number)))

order by (round(case when po_amount = 0 then 0 else nvl(inv_total/po_amount,0) end, 2))*100 desc

</code>

 

As you can see, the SQL is fairly complicated.  I need to find a way to keep the 'TOTAL' out of the sort.

Any ideas?

Thank you in advance!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points