2 Replies Latest reply: Sep 10, 2013 11:04 AM by Greg Spall RSS

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

    V Rickert

      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!