Инструкция create view должна быть единственной в пакете

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?

Интеллектуальная рекомендация

Вам также может понравиться

Это первый раз, когда я пытаюсь создать представление в t-sql, но получаю сообщение об ошибке: CREATE VIEW должен быть единственным оператором в пакете.

Почему он выдает мне эту ошибку и как ее можно исправить?

Я посмотрел на несколько примеров, но все еще не уверен, что мне нужно делать.

CREATE PROCEDURE [dbo].[League_Table_Insert]
    @LeagueName VARCHAR(30)
AS
SET NOCOUNT ON
BEGIN

    CREATE VIEW League_Table AS  
    SELECT
        TeamName AS Team, 
        COUNT(*) Played, 
        COUNT(CASE WHEN HomeScore > AwayScore THEN 1 END) Wins, 
        COUNT(CASE WHEN AwayScore > HomeScore THEN 1 END) Losses, 
        COUNT(CASE WHEN HomeScore = AwayScore THEN 1 END) Draws, 
        SUM(HomeScore) Goals_Scored, 
        SUM(AwayScore) Goals_Against, 
        SUM(HomeScore) - SUM(AwayScore) Goal_Difference,
        SUM(
              CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END 
            + CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END
        ) Points

    FROM
    (
        SELECT TeamName, HomeScore, AwayScore FROM dbo.Team t INNER JOIN dbo.Fixture f ON t.TeamID = f.HomeTeamID INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID WHERE LeagueName = @LeagueName
        UNION ALL
        SELECT TeamName, HomeScore, AwayScore FROM dbo.Team t INNER JOIN dbo.Fixture f ON t.TeamID = f.HomeTeamID INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID WHERE LeagueName = @LeagueName
    ) matches

    GROUP BY TeamName
    ORDER BY Points DESC, Goal_Difference DESC;

Вы не можете создать VIEW внутри функции. Кроме того, вы не можете использовать переменную внутри VIEW. Вам нужна функция с табличным значением. Вот сценарий создания встроенной функции с табличным значением (iTVF):

CREATE FUNCTION dbo.League_Table (
    @LeagueName     VARCHAR(MAX)
) 
RETURNS TABLE
AS
RETURN

SELECT
    TeamName AS Team, 
    COUNT(*) Played, 
    COUNT(CASE WHEN HomeScore > AwayScore THEN 1 END) Wins, 
    COUNT(CASE WHEN AwayScore > HomeScore THEN 1 END) Losses, 
    COUNT(CASE WHEN HomeScore = AwayScore THEN 1 END) Draws, 
    SUM(HomeScore) Goals_Scored, 
    SUM(AwayScore) Goals_Against, 
    SUM(HomeScore) - SUM(AwayScore) Goal_Difference,
    SUM(
          CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END 
        + CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END
    ) Points

FROM
(
    SELECT TeamName, HomeScore, AwayScore FROM dbo.Team t INNER JOIN dbo.Fixture f ON t.TeamID = f.HomeTeamID INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID WHERE LeagueName = @LeagueName
    UNION ALL
    SELECT TeamName, HomeScore, AwayScore FROM dbo.Team t INNER JOIN dbo.Fixture f ON t.TeamID = f.HomeTeamID INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID WHERE LeagueName = @LeagueName
) matches

GROUP BY TeamName;

Обратите внимание, что я удалил предложение ORDER BY, потому что оно не разрешено во встроенных функциях. В противном случае вы получите сообщение об ошибке:

Предложение ORDER BY недопустимо в представлениях, встроенных функциях, производных таблицах, подзапросах и общих табличных выражениях, если также не указано TOP, OFFSET или FOR XML.

Затем, чтобы использовать его:

SELECT * 
FROM dbo.League_Table('Sample League Name') s
ORDER BY Points DESC, Goal_Difference DESC;

person
Felix Pamittan
  
schedule
15.06.2016