I’m trying to make a view. So far, I have written this:
with ExpAndCheapMedicine(MostMoney, MinMoney) as
(
select max(unitprice), min(unitprice)
from Medicine
)
,
findmostexpensive(nameOfExpensive) as
(
select tradename
from Medicine, ExpAndCheapMedicine
where UnitPrice = MostMoney
)
,
findCheapest(nameOfCheapest) as
(
select tradename
from Medicine, ExpAndCheapMedicine
where UnitPrice = MinMoney
)
CREATE VIEW showing
as
select tradename, unitprice, GenericFlag
from Medicine;
Unfortunately, I get an error on the line containing CREATE VIEW showing
«CREATE VIEW must be the only statement in the batch»
How can I fix this?!
Mozart
2,1372 gold badges20 silver badges38 bronze badges
asked Dec 3, 2014 at 12:50
1
Just as the error says, the CREATE VIEW
statement needs to be the only statement in the query batch.
You have two option in this scenario, depending on the functionality you want to achieve:
-
Place the
CREATE VIEW
query at the beginningCREATE VIEW showing as select tradename, unitprice, GenericFlag from Medicine; with ExpAndCheapMedicine(MostMoney, MinMoney) as ( select max(unitprice), min(unitprice) from Medicine ) , findmostexpensive(nameOfExpensive) as ( select tradename from Medicine, ExpAndCheapMedicine where UnitPrice = MostMoney ) , findCheapest(nameOfCheapest) as ( select tradename from Medicine, ExpAndCheapMedicine where UnitPrice = MinMoney )
-
Use
GO
after the CTE and before theCREATE VIEW
query— Option #2
with ExpAndCheapMedicine(MostMoney, MinMoney) as ( select max(unitprice), min(unitprice) from Medicine ) , findmostexpensive(nameOfExpensive) as ( select tradename from Medicine, ExpAndCheapMedicine where UnitPrice = MostMoney ) , findCheapest(nameOfCheapest) as ( select tradename from Medicine, ExpAndCheapMedicine where UnitPrice = MinMoney ) GO CREATE VIEW showing as select tradename, unitprice, GenericFlag from Medicine;
answered Dec 3, 2014 at 12:52
Radu GheorghiuRadu Gheorghiu
20.1k16 gold badges72 silver badges107 bronze badges
9
I came across this question when I was trying to create a couple of views within the same statement, what worked well for me is using dynamic SQL.
EXEC('CREATE VIEW V1 as SELECT * FROM [T1];');
EXEC('CREATE VIEW V2 as SELECT * FROM [T2];');
answered Jan 8, 2019 at 9:36
MozartMozart
2,1372 gold badges20 silver badges38 bronze badges
1
You can also use :
CREATE VIEW vw_test1 AS SELECT [Name] FROM dbo.test1;
GO
CREATE VIEW vw_test2 AS SELECT [Name] FROM dbo.test2;
GO
--If you need to grant some rights, just use :
GRANT SELECT ON vw_test....
It’s easy to understand and avoid dynamic SQL (even if dynamic SQL also works )
answered Jan 25, 2021 at 11:02
AlexBAlexB
7,30212 gold badges56 silver badges75 bronze badges
Simply use GO before and after creating view it will solve your problem
GO
Create view Vaccinated as
select
location,
population,
vaccination
from Covid
GO
answered Dec 14, 2022 at 6:13
1
use statement terminator ; or use GO after cte statement
answered Feb 4 at 12:36
0
Funny, my issue, creating this error was a missing comma in the select fields )))
Like this:
CREATE VIEW [dbo].[ITEM_VIEW]
AS
SELECT dbo.ORDR.one, CAST(dbo.ORDR.two as varchar) AS order_number
dbo.RDR1.three AS line_number
FROM dbo.RDR1 INNER JOIN
dbo.ORDR ON dbo.RDR1.DocEntry = dbo.ORDR.DocEntry
should be
CREATE VIEW [dbo].[ITEM_VIEW]
AS
SELECT dbo.ORDR.one, CAST(dbo.ORDR.two as varchar) AS order_number,
dbo.RDR1.three AS line_number
FROM dbo.RDR1 INNER JOIN
dbo.ORDR ON dbo.RDR1.DocEntry = dbo.ORDR.DocEntry
answered Jan 27 at 11:16
tomtom
2,1901 gold badge23 silver badges27 bronze badges
Basically its what the title says. This is my code.
USE Assignment2;
GO
/* Player View (2 marks)
Create a view which shows the following details of all players:
• The ID number of the player
• The first name and surname of the player concatenated and given an alias of “full_name”
• The team ID number of the player (if applicable)
• The team name of the player (if applicable)
• The coach ID number of the player (if applicable)
• The name of the player’s coach (if applicable)
Creating this view requires a select statement using multiple joins and concatenation of names.
Make sure that you use the appropriate type of join to ensure that players without teams or coaches are still included in the results.
*/
-- Write your Player View here
PRINT 'Creating Player View'
CREATE VIEW playerView AS
SELECT player.id, player.firstName + ' ' + player.surname AS 'Full name', player.team, team.name, player.coach, coach.firstName, coach.surname
FROM player
LEFT OUTER JOIN team
ON player.team = team.id
LEFT OUTER JOIN player as coach
ON player.coach = coach.id;
GO
/* Race View (3 marks)
Create a view which shows the following details of all races:
• All of the columns in the race table
• The name of the race type, course and team involved in the race
• The full name of the player observing the race and the full name of the MVP (if applicable)
• A calculated column with an alias of “unpenalised_score”, which adds the points penalised to the final score
Creating this view requires a select statement using multiple joins and concatenation of names.
Make sure that you use the appropriate type of join to ensure that races without MVPs are still included in the results.
*/
-- Write your Race View here
PRINT 'Creating Race View'
CREATE VIEW raceView AS
SELECT race.id, race.dateOfRace, race.raceType, raceType.name AS raceTypeName, race.course, course.name AS courseName, race.team, team.name AS teamName, race.observer, obs.firstName + ' ' + obs.surname AS observer_name, race.mvp, mvp.firstName + ' ' + mvp.surname AS mvp_name, race.pointsPenalised, race.finalScore + race.pointsPenalised AS unpenalised_score, race.finalScore
FROM race
INNER JOIN raceType
ON race.raceType = raceType.id
INNER JOIN course
ON race.course = course.id
INNER JOIN team
ON race.team = team.id
LEFT OUTER JOIN player AS mvp
ON race.mvp = mvp.id
LEFT OUTER JOIN player AS obs
ON race.observer = obs.id;
GO
SELECT *
FROM playerView
SELECT *
FROM raceView
/* Additional Information:
The views are very convenient replacements for the tables they represent, as they include the names and calculated values that you will often need in queries.
You are very much encouraged to use the views to simplify the queries that follow. You can use a view in a SELECT statement in exactly the same way as you can use a table.
If you wish to create additional views to simplify the queries which follow, include them in this file.
*/
When I run each CREATE VIEW
separately, it seems to run it correctly with no errors. But when I try to run the entire script, it gives me this error.
Msg 111, Level 15, State 1, Line 20
‘CREATE VIEW’ must be the first statement in a query batch.
Msg 111, Level 15, State 1, Line 15
‘CREATE VIEW’ must be the first statement in a query batch.
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘playerView’.
Before attempting to run this script, I first delete database, recreate the tables, populate them and then run this script.
Any ideas where I’m going wrong?
#sql #sql-server #view #ssms
#sql #sql-сервер #Вид #ssms
Вопрос:
Microsoft SQL Server Management Studio 18 показывает ошибку:
CREATE VIEW должна быть единственной инструкцией в пакете
После выполнения запроса появляется следующая ошибка:
Неправильный синтаксис вокруг ключевого слова «select»
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= '1996-05-01'
and l_shipdate < dateadd(mm,3,cast('1996-05-01' as datetime))
group by
l_suppkey;
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey
option (maxdop 2)
drop view revenue0
UPD. Я попытался запустить этот метод:
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= cast('1996-05-01' as datetime)
and l_shipdate < dateadd(mm, 3, cast('1996-05-01' as datetime))
group by
l_suppkey;
go
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey;
drop view revenue0;
Но в результате выполнения запроса отображается ошибка:
Недопустимое имя объекта «revenue0».
Как только я не изменил свое имя. SQL все равно ругается на это имя.
UPD2. Вопрос был решен самостоятельно. Тема закрыта! Спасибо всем за ваши усилия!
Комментарии:
1. Поместите слово
GO
после определения представления.2. Можете ли вы сказать мне, где именно? Я не очень хорошо разбираюсь в SQL. Я буду очень благодарен!
3.
group by l_suppkey; GO
4. Неправильный синтаксис вокруг конструкции «go».
5. вы не можете использовать «order by» в представлении, измените свой вид, чтобы не использовать его.
Ответ №1:
Ошибка сообщает, что CREATE VIEW должен быть единственным оператором в пакете. Пакет завершается в SQL Server ключевым словом «GO», как заявил Гордон, поэтому ваш код должен выглядеть следующим образом:
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= '1996-05-01'
and l_shipdate < dateadd(mm,3,cast('1996-05-01' as datetime))
group by
l_suppkey;
GO -- right here. This ends a batch. Must be on a new line, with no semi-color, or SQL gets pissy.
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey
option (maxdop 2);
drop view revenue0;
Комментарии:
1. Я тестирую «Тесты TPC-H на MS-SQL-Server» этот материал: ссылка . При выполнении этого запроса это должно быть: link .
2. Но у меня, с вашей поправкой, после выполнения SQL-запроса выводится пустая таблица. Время выполнения запроса составляет 0 секунд. Это ненормально.
3. Вы можете помочь переделать этот запрос, чтобы он работал: ссылка .
4. Я уже всю голову сломал с ним. Но этот конкретный запрос не хочет работать. Все остальные работают.
- Remove From My Forums
-
Question
-
I have a problem that I have been unable to solve. I have a stored procedure in a database on server A that needs to create an indexed view in a database on server B. (The environment is SQL Server 2005.) The procedure on server A does
the following: (The EXEC statement inside the quotes is really a variable in my procedure, I’ve just substituted the value to make the example understandable.)EXEC sp_executesql ‘EXEC
[ServerB].[Database].dbo.sp_executesql @sqlStatement;’The «outer» sp_executesql causes the «inner» sp_executesql to be execute within the database on server B. (@sqlStatement contains the CREATE VIEW syntax.) I am able to create the view that I want to index with no problem. However, when
I attempt to create an index on the view in a subsequent step, it fails with the message «Cannot create index. Object ‘_dta_mv_51’ was created with the following SET options off: ‘QUOTED_IDENTIFIER’«. Fair enough,
but if I attempt to precede my CREATE VIEW statement with the required QUOTED_IDENTIFIER, I receive an error to the effect that «‘CREATE VIEW’ must be the first statement in a query batch«.I’m not sure how to get around this problem. Does anyone have the answer? SQL Server 2008 doesn’t object to indexing a view that was created with QUOTED_IDENTIFIER off, but that’s not doing me any good.
Thanks.
Answers
-
> Here is what my testing has shown. First, an indexed view consists of two things, the view and the index on the view. To create the indexed view, you would issue a CREATE VIEW followed by a CREATE INDEX. In SS 2005, you must
precede the CREATE VIEW with a SET QUOTED_IDENTIFIER ON statement. However, in SS 2008, this is not required.Again, this has nothing to do with the version of SQL Server. Both SQL 2005 and SQL 2008 save the setting of QI with the view definition, and both require QI to be ON for an indexed view.
This script fails on both SQL 2005 and SQL 2008:
USE tempdb go CREATE TABLE mybasetable (a int NOT NULL PRIMARY KEY) go SET QUOTED_IDENTIFIER OFF go CREATE VIEW myview WITH SCHEMABINDING AS SELECT a FROM dbo.mybasetable WHERE a > 0 go CREATE UNIQUE CLUSTERED INDEX vix ON myview(a) go DROP VIEW myview DROP TABLE mybasetable go SET QUOTED_IDENTIFIER ON
You can do the nested EXEC kludge, but the real problem is why QUOTED_IDENTIFIER is OFF on your SQL Server 2005 machine. It shouldn’t be.
You said that you createing this from a stored procedure (which I think is a dubious practice), but that is another story. Could you disclose the full code? I got the impression that were linked servers involved, could you confirm this? If that is the case,
I would think there is something bad with the definition of the linked server.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx-
Marked as answer by
Wednesday, September 22, 2010 7:24 AM
-
Marked as answer by
Вопрос 26: Представления
Представление — это виртуальная таблица, содержимое которой определяется запросом.
Преимущества:
- Создает контролируемую (управляемую) среду, позволяет пользователям сосредоточиться на данных.
- Скрывает сложность запросов к данным.
- Обеспечивает безопасность, является объектом промежуточного слоя.
- Организует данные сложных запросов, соединяющих несколько таблиц.
- Производительность View зависит от сложности запроса, лежащего в его основе.
Создание представлений
Синтаксис
CREATE VIEW view_name [(column_list)]
[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}]
AS select_statement
[WITH CHECK OPTION]
Инструкция CREATE VIEW должна быть единственной инструкцией пакета. (Это означает, что эту инструкцию следует отделять от других инструкций группы посредством инструкции GO.)
Параметр view_name задает имя определяемого представления, а в параметре column_list указывается список имен, которые будут использоваться в качестве имен столбцов представления. Если этот необязательный параметр опущен, то используются имена столбцов таблиц, по которым создается представление. Параметр select_statement задает инструкция SELECT, которая извлекает строки и столбцы из таблиц (или других представлений). Параметр WITH ENCRYPTION задает шифрование инструкции SELECT, повышая таким образом уровень безопасности системы баз данных.
Предложение SCHEMABINDING привязывает представление к схеме таблицы, по которой оно создается. Когда это предложение указывается, имена объектов баз данных в инструкции SELECT должны состоять из двух частей, т.е. в виде schema.db_object, где schema — владелец, а db_object может быть таблицей, представлением или определяемой пользователем функцией.
Пример создания:
USE Northwind
GO
CREATE VIEW dbo.EmployeeView
AS
SELECT LastName, Firstname
FROM Employees
Изменение и удаление представлений
Для изменения определения представления в языке Transact-SQL применяется инструкция ALTER VIEW. Синтаксис этой инструкции аналогичен синтаксису инструкции CREATE VIEW, применяющейся для создания представления.
Пример использования ALTER
GO
ALTER VIEW view_WithoutBudget
AS SELECT Number, ProjectName
FROM Project
WHERE Number >= 'p3';
При удалении представления инструкцией DROP VIEW все другие представления, основанные на удаленном, также удаляются, как показано в примере ниже:
DROP VIEW view_Consultant;
При удалении базовой таблицы представления, основанные на ней другие представления, не удаляются автоматически.