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?
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,0992 gold badges19 silver badges37 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
19.9k16 gold badges72 silver badges105 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,0992 gold badges19 silver badges37 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,26212 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
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 badge22 silver badges27 bronze badges
use statement terminator ; or use GO after cte statement
answered Feb 4 at 12:36
- 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
#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. Я уже всю голову сломал с ним. Но этот конкретный запрос не хочет работать. Все остальные работают.
[x] Bug report.
[ ] Feature request.
It’s not possible to deploy a view into another database, using ssc push production
Gives this error code:
It looks like the current situation doesn’t allow to run two statements in a single batch.
if exists (select * from sys.objects where object_id = object_id('[sys].[database_firewall_rules]') and type = 'V')
drop view [sys].[database_firewall_rules]
go
CREATE VIEW sys.database_firewall_rules AS SELECT id, name, start_ip_address, end_ip_address, create_date, modify_date FROM sys.database_firewall_rules_table
Expected to create the view for me.
Environment: Azure SQL Database (latest version)
ssc pull development
ssc push production