order by in a union

From: Marc Miller <mmiller_at_epix.net>
Date: Wed, 31 May 2006 17:30:17 GMT
Message-ID: <Jokfg.9183$lb.830635@news1.epix.net>

Hello all,

I am having a heck of a time getting a UNION to ORDER BY. I'm working with SQL Server Reporting Services,
but even when I try the query in SQL*Plus, I receive the same error which is 'SQL command not properly ended'
and it point to the "O" in word "ORDER" on the last line of the query.

I've tried "ORDER BY project, accounting_date" and I've trime "ORDER BY 3,6" and I've even tried just
"ORDER BY project", "ORDER BY d.project" and "ORDER BY 2". Same error each time.

Here are my syntax (which work fine without the "ORDER BY " clause.

Select poh.SEGMENT1 AS PO_NUM, d.VENDOR_NAME,SUBSTR(d.project,1,3) || '-' || SUBSTR(d.project,4,2) || '-' || SUBSTR(d.project,6,5) || '-' || SUBSTR(d.project,11,4) AS PROJECT,
d.TASK_NUMBER, d.PERIOD_NAME, to_date(d.period_name,'MM-RR') as accounting_date, d.AMOUNT, d.project_id, d.po_distribution_id, d.description, d.dist_code_combination_id, pod.po_distribution_id

FROM PO.PO_HEADERS_ALL poh , conv.ap_proj  d, PO.Po_distributions_all  pod
WHERE d.po_distribution_id  = pod.po_distribution_id
AND pod.PO_HEADER_ID = poh.PO_HEADER_ID

AND d.project like ('50102800%')
AND to_date(d.period_name,'MM-RR') between to_date('JAN-98','MM-RR') ORDER BY d.project, d.period_name add_months(to_date('JAN-06','MM-RR'),1)

     UNION ALL
SELECT '****' as po_num, d.VENDOR_NAME, SUBSTR(d.project,1,3) || '-' || SUBSTR(d.project,4,2) || '-' || SUBSTR(d.project,6,5) || '-' || SUBSTR(d.project,11,4) AS PROJECT,
d.TASK_NUMBER, d.PERIOD_NAME, to_date(d.period_name,'MM-RR') as accounting_date, d.AMOUNT, d.project_id, d.po_distribution_id, d.description,d.dist_code_combination_id, 0 as po_distribution_id
FROM conv.ap_proj d
WHERE d.project like ('50102800%')
AND to_date(d.period_name,'MM-RR') between to_date('JAN-98','MM-RR') AND add_months(to_date('JAN-06','MM-RR'),1) AND po_distribution_id IS NULL order by accounting_date, project  ORDER BY project, accounting_date
/

TIA,
Marc Miller Received on Wed May 31 2006 - 12:30:17 CDT