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 3 7965
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**********@b tinternet.com> wrote in message
news:81******** *************** ***@posting.goo gle.com... 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" <gu******@nospa m-online.sbcgloba l.net> wrote in message news:<G8******* ************@ne wssvr24.news.pr odigy.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**********@b tinternet.com> wrote in message news:81******** *************** ***@posting.goo gle.com...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" <gu******@nospa m-online.sbcgloba l.net> wrote in message news:<G8******* ************@ne wssvr24.news.pr odigy.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**********@b tinternet.com> wrote in message news:81******** *************** ***@posting.goo gle.com...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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Joe Stevenson |
last post by:
Hi all,
I skimmed through the docs for Python, and I did not find anything like
a case or switch statement. I assume there is one and that I just
missed it. Can someone please point me to the appropriate document, or
post an example? I don't relish the idea especially long if-else
statements.
Joe
|
by: Chad Richardson |
last post by:
I've always been mistified why you can't use a column alias in the group by
clause (i.e. you have to re-iterate the entire expression in the group by
clause after having already done it once in the select statement). I'm
mostly a SQL hobbiest, so it's possible that I am not doing this in the most
efficient manner. Anyone care to comment on this with relation to the
following example (is there a way to acheive this without re-stating the...
|
by: mmm |
last post by:
Hello,
I wrote the following statement:
SELECT CASE
WHEN (SELECT count(*) FROM employee where empno='000010')>0 THEN
'true'
ELSE 'false'
END AS isExist
from employee where empno='000010'
|
by: Arne |
last post by:
A lot of Firefox users I know, says they have problems with validation
where the ampersand sign has to be written as & to be valid. I don't
have Firefox my self and don't wont to install it only because of this,
so I hope some of you gurus can enlighten me with this :)
In what circumstances can the "&" in the source code be involuntary
changed to "&" by a browser when or other software, when editing and
uploading the file to the web...
|
by: alf |
last post by:
Hi,
is it possible that due to OS crash or mysql itself crash or some e.g.
SCSI failure to lose all the data stored in the table (let's say million
of 1KB rows). In other words what is the worst case scenario for MyISAM
backend?
Also is it possible to not to lose data but get them corrupted?
| |
by: juan.gautier |
last post by:
Hi,
I try to construct a SQL code for a view to select a specific data
from a table, this query take the value of the filter from a text box
in a visual basic 6.0 form. my problem is when i DECLARE the variable
@AMP receive a error message that said "The Declare cursor SQL
construct or statement is not supported." What i do wrong? Please help
me. i attach the code.
DECLARE @AMP int;
|
by: Mudiya Dissa |
last post by:
Hi,
I have some code on my application like the following
<%
redirectUrl = "mypage.asp?type=100&id=something&view=10
'some more asp code
|
by: NoncentzTheGreat |
last post by:
Hello All, This is me first Post ..Yesssss
I am new to sql so I hope I explain this correctly:
I used a PATINDEX statement to create a field called UPS_Shipper in my view and a field called UPSCodeShipTo which is renamed from a field I pulled earlier.
What I have been tasked to do it to create a statement that will do the following:
If there is a value in UPS_CodeShipTo (which there always is) then I need to use that value in a new...
|
by: Adem |
last post by:
C/C++ language proposal:
Change the 'case expression' from "integral constant-expression" to "integral expression"
The C++ Standard (ISO/IEC 14882, Second edition, 2003-10-15)
says under 6.4.2(2) :
case constant-expression :
I propose that the case expression of the switch statement
be changed from "integral constant-expression" to "integral expression".
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |