Problem with order by and Union

[lovalles]

i have the following command:

select item_no,left(request_dt,6)as periodo , qty_remaining From poordlin_sql
WHERE STK_LOC='02' And ord_status<>'N' And qty_ordered>qty_received and left(request_dt,6)<=(200111)

UNION

Select item_no,periodo,qtyr as qty_remaining from(
select distinct ord_no,line_no,item_no,left(request_dt,6)as periodo ,
CASE
(SELECT TOP 1 ORD_STATUS FROM POHDRHST_SQL WHERE POHDRHST_SQL.ORD_NO=P1.ORD_NO
*ORDER BY pohdrhst_sql.hst_dt desc,pohdrhst_sql.hst_tm DESC)
when 'C' THEN 0
else (select top 1 qty_remaining from polinhst_sql p2 where p2.ord_no=p1.ord_no and p2.line_no=p1.line_no
* order by polinhst_sql.hst_dt desc, polinhst_sql.hst_tm desc)
end as qtyR
From polinhst_SQL as p1
WHERE STK_LOC='02' And ord_status<>'N' And qty_ordered>qty_received and left(request_dt,6)<= (200111)) as r

This lines gives me the error Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.

now if i delete the 2 line marked with * ( the order by clauses ) the union works fine, i just need those 2 subqueries to be ordered.
what can i do?

 

[JimmySkliff]

The ORDER BY clause can include items not appearing in the select list; however, if SELECT DISTINCT is specified or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

Furthermore, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list

I most certainly hope this tidbit of info helps....

 

[lovalles]

if you see the first query before the union and the results of the second one, the columns are exacly the same.
The order i am using is on a subquery not the result of the second query, Why is this making conflic?
what should i do?

 

[rac2]

What is this dangling off the end of the statement?

)) as r

 

[lovalles]

Because i am going to do after i get a success union a GROUP BY of the query of the union

 

[jhall156]

Have you tried using MAX() instead of Top 1 in your subqueries? I started out with nothing, and I still have most of it.