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?
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?