order by in a union

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


.

Relevant Pages

  • Re: order by in a union
    ... Here's the real syntax: ... I am having a heck of a time getting a UNION to ORDER BY. ... SQL Server Reporting Services, ... Here are my syntax (which work fine without the "ORDER BY " clause. ...
    (comp.databases.oracle.server)
  • Re: order by in a union
    ... I am having a heck of a time getting a UNION to ORDER BY. ... SQL Server Reporting Services, ... Here are my syntax (which work fine without the "ORDER BY " clause. ...
    (comp.databases.oracle.server)
  • Re: Automatically Printing from SQL Reporting services
    ... Local printing will be in the next service pack. ... MVP SQL Server Reporting Services ... "Ernie" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: Migrating existing SQL application to sharepoint
    ... Investigate SQL Server Reporting Services for your reporting needs, ... on a server and refer to it using a web viewer part. ... > way to migrate this application into sharepoint? ...
    (microsoft.public.sharepoint.portalserver.development)
  • RE: identity impersonate in Sharepoint 2003
    ... We already handoff the web service request itself to another assembly. ... We wish to connect to the SQL Server Reporting Services using the logged on ... > is to create a utilities class that includes impersonation. ...
    (microsoft.public.sharepoint.portalserver.development)