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,1072 gold badges19 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
20k16 gold badges72 silver badges106 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,1072 gold badges19 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,29212 gold badges55 silver badges74 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. Я уже всю голову сломал с ним. Но этот конкретный запрос не хочет работать. Все остальные работают.
Как показано на рисунке выше, SQL Server выдает сообщение об ошибке.
Из онлайновой информации видно, что оператор CREATE VIEW имеет условие, что оператор должен быть выполнен первым, и на рисунке есть использование xsgl, поэтому система выдает ошибку.Решение таково: добавьте инструкцию GO для брата выше и ниже инструкции CREATE VIEW.
Интеллектуальная рекомендация
Механизм суждения о мусоре JVM
Механизм суждения о мусоре JVM 1. Метод подсчета цитирования (в основном не используется) Добавьте справочный счетчик в объект. Когда есть место для его упоминания, значение счетчика равна 1 Когда эта…
Qt
Краткое описание QPainterPath( ), , , : 、 、 。 , — , QPainterPathStroker。 | : 、 , 。 QPainterPathStroker , : QPainterPath path, QPainterPathStroker::createStroke(path), path QPainterPath outlinePath,out…
C Note(NO.1)
Ключевые слова: Void, функция: объявления без возвращаемого значения или функции параметров, а оператор не имеет указателя типа. Чар, функция: данные типа символа. Int, функция: целочисленные данные, …
Распределение IBC и Палитра печати CU в HEVC-SCC
Для кодирования изображения содержимого экрана внутреннее предсказание включает в себя обычный режим внутреннего предсказания Intra, внутриблочное копирование IntraBlockCopy и режим палитры Palette. Л…
Вам также может понравиться
Я пытаюсь сделать вид. До сих пор я писал следующее:—3—>
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;
к сожалению, я получаю ошибку в строке, содержащей CREATE VIEW showing
«CREATE VIEW должен быть единственным оператором в пакете»
Как я могу это исправить?!
1 ответов
так же, как ошибка говорит, должен быть единственным оператором в пакете запросов.
у вас есть два варианта в этом сценарии, в зависимости от функциональности, которую вы хотите достичь:
-
место
CREATE VIEW
запрос в началеCREATE 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 )
-
использовать
GO
после CTE и доCREATE VIEW
запрос— 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;