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

CASE SQL Construct

I am using the following code to construct an SQL 7.0 View Column

"CASE WHEN [DailyHours] > SUM([TransAmt]) THEN 0 ELSE 1 END"

and it works great!!

However, when I try the same line in SQL 2000, I get the message "The query designer does not support the case SQL construct"

The help screen is no help as all it says is "the syntax you entered is valid but is not supported visually by Query Designer. Be sure the verify your syntax before saving."

It will not let me save, so I'm not sure what to do from here now??You need to write your query in Query Analyzer, not the Query Designer, which is limited in the types of query logic it can represent in its GUI interface.
No self-respecting DBA writes queries in Query Designer. Time to take off the training wheels...|||You are right, I shouldn't be using Query Designer.... here's my problem.

My client is in another city, and I am logging onto their system remotely to copy some code I've written using a VPN they set up for me on my lap top...

The only software I have on my lap top is designer ... which is lame I know.

I have Visual Studio on my primary system...... but no simple way to put this code on their system - ANY thoughts?|||Why don't you use osql?|||No self-respecting DBA writes queries in Query Designer. Time to take off the training wheels...

You taking midol today?|||You taking midol today?
Just my usual grace and elegance.|||Just rename the file to remove the .txt extension|||I opened the file and I get a black screen that looks like an old DOS prompt, which is asking for a password. No matter what I enter it doesn't accept, and a blank simply closes the window....... I'm confused..... what's the password - my windos password or something specific that I have yet to learn??|||at a command window type 'osql.exe /?' it will give you a quick help message on how to use osql. Once you are in to the server, you can execute t-sql commands from there. Look it up in BOL if you need more help.