«CREATE VIEW» должен быть первым оператором в пакете запросов

17

В основном это то, что говорится в названии. Это мой код.

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.
*/

Когда я запускаю каждый CREATE VIEW отдельно, он, кажется, запускает его правильно, без ошибок. Но когда я пытаюсь запустить весь скрипт, он дает мне эту ошибку.

  

Msg 111, уровень 15, состояние 1, строка 20
  «CREATE VIEW» должен быть первым оператором в пакете запросов.
  Msg 111, уровень 15, состояние 1, строка 15
  «CREATE VIEW» должен быть первым оператором в пакете запросов.
  Msg 208, уровень 16, состояние 1, строка 2
  Недопустимое имя объекта 'playerView'.

Прежде чем пытаться запустить этот скрипт, я сначала удаляю базу данных, воссоздаю таблицы, заполняю их и запускаю этот скрипт.

Любые идеи, в которых я ошибаюсь?

    
задан Shivarn 12.11.2012 в 09:21
источник

4 ответа

25

поставьте GO после PRINT 'Creating Player View' и он должен работать:

PRINT 'Creating Player View'
GO

CREATE VIEW playerView AS
    
ответ дан Omar Jackman 12.11.2012 в 09:22
источник
15

Пакеты разделяются словом GO - которое является инструкцией для клиентских инструментов, а не SQL Server, в частности, чтобы сообщить этим инструментам, как разделить ваш запрос на партии.

Ошибка говорит вам, что CREATE VIEW должно быть первым оператором в пакете:

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'

GO -->-- New GO here

CREATE VIEW playerView AS 

Итак, я добавил GO до CREATE VIEW

    
ответ дан Damien_The_Unbeliever 12.11.2012 в 09:24
источник
1

Поместите код CREATE VIEW внутри EXECUTE

SOME CONDITION..

EXECUTE('CREATE  VIEW vwName...')
    
ответ дан kirk 07.06.2017 в 12:03
источник
0

Обычно это происходит из-за возможности создания VIEW или любого DBO, вам нужен весь скрипт внутри транзакции или вам нужно включить SET QUOTED_IDENTIFIER.

т

USE [yourdatabase]
GO

SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
SET ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRANSACTION
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

...
...

-- Write your Race View here
PRINT 'Creating Race View'
GO

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 

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END
GO

IF @@TRANCOUNT>0 COMMIT TRANSACTION
GO

SET NOEXEC OFF
    
ответ дан Fandango68 28.06.2017 в 02:05
источник