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?

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's user avatar

Mozart

2,0992 gold badges19 silver badges37 bronze badges

asked Dec 3, 2014 at 12:50

Kadaj13's user avatar

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:

  1. Place the CREATE VIEW query at the beginning

    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
        )
    
  2. Use GO after the CTE and before the CREATE 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 Gheorghiu's user avatar

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

Mozart's user avatar

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

AlexB's user avatar

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

SAURABH AHALAWAT's user avatar

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

tom's user avatar

tomtom

2,1901 gold badge22 silver badges27 bronze badges

use statement terminator ; or use GO after cte statement

answered Feb 4 at 12:36

SAURABH AHALAWAT's user avatar

  • 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_executesqlEXEC
    [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

#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

Понравилась статья? Поделить с друзьями:
  • Gmca20 04 инструкция по эксплуатации на русском языке
  • Profiwood антисептик для дерева инструкция по применению
  • Руководство пользователя это что в программировании
  • Если я не согласна с должностной инструкцией что делать
  • Вазокет 600 инструкция по применению цена отзывы аналоги таблетки взрослым