Re: order by in a union

Hi,

In set operator, Order by clause not working.

Tharanidharan.E

Marc Miller wrote:
> Never mind and thanks for any time you spent trying to read this. I see my
> error and I've fixed
> it. False Alarm and much chagrin.
>
> Marc
>
>
>
> "Marc Miller" <mmiller_at_epix.net> wrote in message
> news:LGkfg.9184$lb.830691_at_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 Thu Jun 01 2006 - 03:45:39 CDT