Re: order by in a union

From: Marc Miller <mmiller_at_epix.net>
Date: Wed, 31 May 2006 17:49:31 GMT
Message-ID: <LGkfg.9184$lb.830691@news1.epix.net>

Sorry, I was experimenting by having an 'ORDER BY' for each select. Meant to strip that out.
Here's the real syntax:

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') and 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

"Jeremy" <jeremy0505_at_gmail.com> wrote in message news:MPG.1ee7e5fad058f1f998a1ec_at_news.individual.net...
> In article <Jokfg.9183$lb.830635_at_news1.epix.net>, Marc Miller says...
>> 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) || '-'
>> ||
>
> <snip>
>
>> AND po_distribution_id IS NULL order by accounting_date, project
>> ORDER BY project, accounting_date
>> /
>>
>
> 2 order by clauses?
>
> --
>
> jeremy
Received on Wed May 31 2006 - 12:49:31 CDT