Case Statement & View

[Bryan]

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 Guzman]

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" <br**********@btinternet.com> wrote in message
news:81**************************@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

[Bryan]

I'm using SQL Server 2000 sp3

thanks

Bryan
"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message news:<G8*******************@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" <br**********@btinternet.com> wrote in message
news:81**************************@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

[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" <gu******@nospam-online.sbcglobal.net> wrote in message news:<G8*******************@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" <br**********@btinternet.com> wrote in message
news:81**************************@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