Cursors with UNION in select doesn't work?

Hi,
I'm trying to open a cursor by selecting datas from an UNION of two tables.
I keep getting the error "incorrect syntax near the keyword 'UNION'".
Here my code...
DECLARE cArticles CURSOR FAST_FORWARD
FOR
select id from vw_articles where isFrontPage = 1 and categoryId = @.cId order
by frontPageDate desc
UNION
select id from vw_articles where isFrontPage = 1 and categoryId in (select
fk_tbl_category_child_id from tbl_subCategories where
fk_tbl_category_parent_id = @.cId) order by frontPageDate desc
OPEN cArticles
...
I tried with parenthesis, temp table... didn't work as well.
Any idea of how I can open a cursor with a select in two tables?
Thanks
StephaneThe problem is not the cursor. The problem is that you have order by within
your union. You can only apply order by on the whole set, not on individual
elements of the union.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:2847989A-9ECA-4500-84F2-82BE1CA089DC@.microsoft.com...
> Hi,
> I'm trying to open a cursor by selecting datas from an UNION of two
> tables.
> I keep getting the error "incorrect syntax near the keyword 'UNION'".
> Here my code...
> DECLARE cArticles CURSOR FAST_FORWARD
> FOR
> select id from vw_articles where isFrontPage = 1 and categoryId = @.cId
> order
> by frontPageDate desc
> UNION
> select id from vw_articles where isFrontPage = 1 and categoryId in (select
> fk_tbl_category_child_id from tbl_subCategories where
> fk_tbl_category_parent_id = @.cId) order by frontPageDate desc
> OPEN cArticles
> ...
> I tried with parenthesis, temp table... didn't work as well.
> Any idea of how I can open a cursor with a select in two tables?
> Thanks
> Stephane
>|||Great! It works!
Thanks a lot
Stephane
"Aaron Bertrand [SQL Server MVP]" wrote:

> The problem is not the cursor. The problem is that you have order by with
in
> your union. You can only apply order by on the whole set, not on individu
al
> elements of the union.
>
>
> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
> news:2847989A-9ECA-4500-84F2-82BE1CA089DC@.microsoft.com...
>
>|||What about:
DECLARE cArticles CURSOR FAST_FORWARD
FOR
SELECT * FROM
(
select id from vw_articles where isFrontPage = 1 and categoryId = @.cId
order
by frontPageDate desc
UNION
select id from vw_articles where isFrontPage = 1 and categoryId in
(select
fk_tbl_category_child_id from tbl_subCategories where
fk_tbl_category_parent_id = @.cId) order by frontPageDate desc
) SubQuery
OPEN cArticles
HTH, Jens Suessmeyer.