June 3, 2011 at 1:58 pm
Hi,
Please advice, I'm buildind a query that return this error, I can't see error line
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator
Use xxxx
GO
select ba,CC,desc, sum(case when Cdban = '01341' then S end) as '01341'
from dbo.M
where fo='A'and m='03'and Cdban = '01341'
Group by ba,CC,desc
union all
select ba,CC,desc, sum(case when Cdban = '01111' then S end) as '01111'
from dbo.M
where fo='A'and m='03'and Cdban = '01111'
Group by ba,CC,desc
order by row_number() over (partition by ba order by CC)
Thanks for your response
June 3, 2011 at 2:02 pm
Well for 1 ROW_Number is not available in sql 2000 so I'll assume 2005.
What happens is that the query is actually treated as such :
SELECT * FROM (
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
) dtUnionAll
ORDER BY ...
To fix you'll have to rewrite the query like I just did and it will work (might have to actually put the RN() function in the select as well... not thinking clearly atm and not 100% sure)...
June 3, 2011 at 2:38 pm
Ok, perfect that is what i was looking for
Thanks for your help
SELECT * FROM (
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
) dtUnionAll
ORDER BY ...
Is possible to pivot this resulting set ?
Thanks for your ideas...
June 3, 2011 at 2:40 pm
luismarinaray (6/3/2011)
Ok, perfect that is what i was looking forThanks for your help
SELECT * FROM (
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
) dtUnionAll
ORDER BY ...
Is possible to pivot this resulting set ?
Thanks for your ideas...
Everything is possible... what do you need?
June 3, 2011 at 9:26 pm
Hi,
Now,I need to pivot from the outer query some row transforms into columns.
Since i need to preserve the order in what data has been loaded I was thinking in
select * from (
SELECT ba,Co,des,[00418]
FROM (SELECT ba,Co,des,Con,Sal from dbo.mov) as D
PIVOT(SUM(Sal) for Con in ([00418])) AS P
union all
SELECT ba,Co,des,[01341]
FROM (SELECT ba,Co,des,Con,Sal from dbo.mov) as D
PIVOT(SUM(Sal) for Con in ([01341])) AS P
)
as r
Order by row_number() over (partition by r.balance_id order by r.CodigoContable)
But always return, the first inner query as titles only, I want to recover this data set plus
second data set side by side.
Does anybody have an Idea or I'm wrong?
:crazy:
Thanks again
June 4, 2011 at 4:29 am
Does the first query actually return anything if your run it on it's own?
June 4, 2011 at 12:27 pm
The first query return that columns pivoted and ok same situation second query, but what I need is to mix both query each pivoted column in that place.
Example:
Ba CC Des 12345 Ba CC Des 09876
OK ?
Any Idea..
Thanks
June 4, 2011 at 12:33 pm
Put only 1 pivot once the union is done.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply