"Stephane" wrote:

> Hi,
>
> I keep receiving this error when I try to create a cursor:
>
> « ORDER BY items must apprear in the select list if the statement contains a
> UNION operator. »
>
> Here's my code
>
> DECLARE cArticles CURSOR FAST_FORWARD
> FOR 
> (select id, dateAdded, nbVote, nbComments from vw_articles where isFrontPage
> = 0 and isEditorial = 0 and categoryId = @cId and dateAdded > dateadd(hh,
> -@nbDays, getDate())
> UNION
> select id, dateAdded, nbVote, nbComments from vw_articles where isFrontPage
> = 0 and isEditorial = 0 and categoryId in
> (select fk_tbl_category_child_id from tbl_subCategories where
> fk_tbl_category_parent_id = @cId))
> order by
> CASE WHEN @sortId = 0 THEN dateAdded END desc,
> CASE WHEN @sortId = 1 THEN dateAdded END,
> CASE WHEN @sortId = 2 THEN nbVote END desc,
> CASE WHEN @sortId = 3 THEN nbVote END,
> CASE WHEN @sortId = 4 THEN nbComments END desc
> OPEN cArticles
>
> Does anyone see the problem? Any idea?
>
> Thanks
>
> Stephane
>

"Mark Williams" wrote:

> Since you are pulling the same columns from the same table, why use UNION at
> all; add an OR condition to the WHERE clause to capture rows that would have
> matched the query after the UNION.
>
> select id, dateAdded, nbVote, nbComments from vw_articles where
>
> (isFrontPage
> = 0 and isEditorial = 0 and categoryId = @cId and dateAdded > dateadd(hh,
> -@nbDays, getDate()))
> OR --UNION
> (isFrontPage = 0 and isEditorial = 0 and categoryId in
> (select fk_tbl_category_child_id from tbl_subCategories where
> fk_tbl_category_parent_id = @cId)) )
>
>
> --
>
> "Stephane" wrote:
>
> > Hi,
> >
> > I keep receiving this error when I try to create a cursor:
> >
> > « ORDER BY items must apprear in the select list if the statement contains a
> > UNION operator. »
> >
> > Here's my code
> >
> > DECLARE cArticles CURSOR FAST_FORWARD
> > FOR 
> > (select id, dateAdded, nbVote, nbComments from vw_articles where isFrontPage
> > = 0 and isEditorial = 0 and categoryId = @cId and dateAdded > dateadd(hh,
> > -@nbDays, getDate())
> > UNION
> > select id, dateAdded, nbVote, nbComments from vw_articles where isFrontPage
> > = 0 and isEditorial = 0 and categoryId in
> > (select fk_tbl_category_child_id from tbl_subCategories where
> > fk_tbl_category_parent_id = @cId))
> > order by
> > CASE WHEN @sortId = 0 THEN dateAdded END desc,
> > CASE WHEN @sortId = 1 THEN dateAdded END,
> > CASE WHEN @sortId = 2 THEN nbVote END desc,
> > CASE WHEN @sortId = 3 THEN nbVote END,
> > CASE WHEN @sortId = 4 THEN nbComments END desc
> > OPEN cArticles
> >
> > Does anyone see the problem? Any idea?
> >
> > Thanks
> >
> > Stephane
> >

"Stephane" <Steph***@discussions.microsoft.com> wrote in message
news:2A4849F3-E5FE-4CFA-BDA5-325AFADEC813@microsoft.com...
> Hi,
>
> I keep receiving this error when I try to create a cursor:
>
> « ORDER BY items must apprear in the select list if the statement contains
> a
> UNION operator. »
>
> Here's my code
>
> DECLARE cArticles CURSOR FAST_FORWARD
> FOR
> (select id, dateAdded, nbVote, nbComments from vw_articles where
> isFrontPage
> = 0 and isEditorial = 0 and categoryId = @cId and dateAdded > dateadd(hh,
> -@nbDays, getDate())
> UNION
> select id, dateAdded, nbVote, nbComments from vw_articles where
> isFrontPage
> = 0 and isEditorial = 0 and categoryId in
> (select fk_tbl_category_child_id from tbl_subCategories where
> fk_tbl_category_parent_id = @cId))
> order by
> CASE WHEN @sortId = 0 THEN dateAdded END desc,
> CASE WHEN @sortId = 1 THEN dateAdded END,
> CASE WHEN @sortId = 2 THEN nbVote END desc,
> CASE WHEN @sortId = 3 THEN nbVote END,
> CASE WHEN @sortId = 4 THEN nbComments END desc
> OPEN cArticles
>
> Does anyone see the problem? Any idea?
>
> Thanks
>
> Stephane
>