Case Statement & View

I am executing a case statement list below,

USE Northwind

SELECT
MONTH(OrderDate) AS OrderMonth,
SUM(CASE YEAR(OrderDate)
WHEN 1996 THEN 1
ELSE 0
END) AS c1996,
SUM(CASE YEAR(OrderDate)
WHEN 1997 THEN 1
ELSE 0
END) AS c1997,
SUM(CASE YEAR(OrderDate)
WHEN 1998 THEN 1
ELSE 0
END) AS c1998
FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)

According to BOL I should be able to save this query as a view.
However when I try to save the query as a view I get a error message
stating

"View definition includes no output columns or includes no items in
the FROM clause"

According to what I have read although the case statement is not
supported via the enterprise query pane, the query should still run
and be saved. In my case however I cannot seem to save it no matter
what I try.

Can anyone shed any light on the matter?

Thanks in advance

BryanWhat version of SQL Server? I get the following error when I try to save
the query as a view using SQL 2000 SP3a:

The ORDER BY clause is invalid in views, inline functions,
derived tables, and subqueries, unless TOP is also specified.

It creates fine when I remove the ORDER BY. I then retrieved the results
using the following query

SELECT *
FROM MyView
ORDER BY OrderMonth

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bryan" <bryanmcguire@.btinternet.com> wrote in message
news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
>I am executing a case statement list below,
> USE Northwind
> SELECT
> MONTH(OrderDate) AS OrderMonth,
> SUM(CASE YEAR(OrderDate)
> WHEN 1996 THEN 1
> ELSE 0
> END) AS c1996,
> SUM(CASE YEAR(OrderDate)
> WHEN 1997 THEN 1
> ELSE 0
> END) AS c1997,
> SUM(CASE YEAR(OrderDate)
> WHEN 1998 THEN 1
> ELSE 0
> END) AS c1998
> FROM Orders
> GROUP BY MONTH(OrderDate)
> ORDER BY MONTH(OrderDate)
>
> According to BOL I should be able to save this query as a view.
> However when I try to save the query as a view I get a error message
> stating
> "View definition includes no output columns or includes no items in
> the FROM clause"
> According to what I have read although the case statement is not
> supported via the enterprise query pane, the query should still run
> and be saved. In my case however I cannot seem to save it no matter
> what I try.
> Can anyone shed any light on the matter?
> Thanks in advance
> Bryan|||I'm using SQL Server 2000 sp3

thanks

Bryan

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<G8G0d.18454$6S7.12715@.newssvr24.news.prodigy.com>...
> What version of SQL Server? I get the following error when I try to save
> the query as a view using SQL 2000 SP3a:
> The ORDER BY clause is invalid in views, inline functions,
> derived tables, and subqueries, unless TOP is also specified.
> It creates fine when I remove the ORDER BY. I then retrieved the results
> using the following query
> SELECT *
> FROM MyView
> ORDER BY OrderMonth
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <bryanmcguire@.btinternet.com> wrote in message
> news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
> >I am executing a case statement list below,
> > USE Northwind
> > SELECT
> > MONTH(OrderDate) AS OrderMonth,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1996 THEN 1
> > ELSE 0
> > END) AS c1996,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1997 THEN 1
> > ELSE 0
> > END) AS c1997,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1998 THEN 1
> > ELSE 0
> > END) AS c1998
> > FROM Orders
> > GROUP BY MONTH(OrderDate)
> > ORDER BY MONTH(OrderDate)
> > According to BOL I should be able to save this query as a view.
> > However when I try to save the query as a view I get a error message
> > stating
> > "View definition includes no output columns or includes no items in
> > the FROM clause"
> > According to what I have read although the case statement is not
> > supported via the enterprise query pane, the query should still run
> > and be saved. In my case however I cannot seem to save it no matter
> > what I try.
> > Can anyone shed any light on the matter?
> > Thanks in advance
> > Bryan|||Dan,

Hi sorry, I just checked I was only running sp 1 not 3a as I posted
earlier. I have now changed to sp3a and ran the query again. This time
I manged to save the view by removing the "Order By"...

Thanks again

Bryan

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<G8G0d.18454$6S7.12715@.newssvr24.news.prodigy.com>...
> What version of SQL Server? I get the following error when I try to save
> the query as a view using SQL 2000 SP3a:
> The ORDER BY clause is invalid in views, inline functions,
> derived tables, and subqueries, unless TOP is also specified.
> It creates fine when I remove the ORDER BY. I then retrieved the results
> using the following query
> SELECT *
> FROM MyView
> ORDER BY OrderMonth
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <bryanmcguire@.btinternet.com> wrote in message
> news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
> >I am executing a case statement list below,
> > USE Northwind
> > SELECT
> > MONTH(OrderDate) AS OrderMonth,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1996 THEN 1
> > ELSE 0
> > END) AS c1996,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1997 THEN 1
> > ELSE 0
> > END) AS c1997,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1998 THEN 1
> > ELSE 0
> > END) AS c1998
> > FROM Orders
> > GROUP BY MONTH(OrderDate)
> > ORDER BY MONTH(OrderDate)
> > According to BOL I should be able to save this query as a view.
> > However when I try to save the query as a view I get a error message
> > stating
> > "View definition includes no output columns or includes no items in
> > the FROM clause"
> > According to what I have read although the case statement is not
> > supported via the enterprise query pane, the query should still run
> > and be saved. In my case however I cannot seem to save it no matter
> > what I try.
> > Can anyone shed any light on the matter?
> > Thanks in advance
> > Bryan