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

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,1372 gold badges20 silver badges38 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

20.1k16 gold badges72 silver badges107 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,1372 gold badges20 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

AlexB's user avatar

AlexBAlexB

7,30212 gold badges56 silver badges75 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

use statement terminator ; or use GO after cte statement

answered Feb 4 at 12:36

SAURABH AHALAWAT's user avatar

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

tom's user avatar

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. Я уже всю голову сломал с ним. Но этот конкретный запрос не хочет работать. Все остальные работают.

  • 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


Вопрос 26: Представления


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

Преимущества:

  • Создает контролируемую (управляемую) среду, позволяет пользователям сосредоточиться на данных.
  • Скрывает сложность запросов к данным.
  • Обеспечивает безопасность, является объектом промежуточного слоя.
  • Организует данные сложных запросов, соединяющих несколько таблиц.
  • Производительность View зависит от сложности запроса, лежащего в его основе.

Создание представлений

Синтаксис

CREATE VIEW view_name [(column_list)]
    [WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}]
    AS select_statement
    [WITH CHECK OPTION]

Инструкция CREATE VIEW должна быть единственной инструкцией пакета. (Это означает, что эту инструкцию следует отделять от других инструкций группы посредством инструкции GO.)

Параметр view_name задает имя определяемого представления, а в параметре column_list указывается список имен, которые будут использоваться в качестве имен столбцов представления. Если этот необязательный параметр опущен, то используются имена столбцов таблиц, по которым создается представление. Параметр select_statement задает инструкция SELECT, которая извлекает строки и столбцы из таблиц (или других представлений). Параметр WITH ENCRYPTION задает шифрование инструкции SELECT, повышая таким образом уровень безопасности системы баз данных.

Предложение SCHEMABINDING привязывает представление к схеме таблицы, по которой оно создается. Когда это предложение указывается, имена объектов баз данных в инструкции SELECT должны состоять из двух частей, т.е. в виде schema.db_object, где schema — владелец, а db_object может быть таблицей, представлением или определяемой пользователем функцией.

Пример создания:

USE Northwind
GO
CREATE VIEW dbo.EmployeeView
AS
SELECT LastName, Firstname
FROM Employees

Изменение и удаление представлений

Для изменения определения представления в языке Transact-SQL применяется инструкция ALTER VIEW. Синтаксис этой инструкции аналогичен синтаксису инструкции CREATE VIEW, применяющейся для создания представления.

Пример использования ALTER

GO
ALTER VIEW view_WithoutBudget
    AS SELECT Number, ProjectName
    FROM Project
    WHERE Number >= 'p3';

При удалении представления инструкцией DROP VIEW все другие представления, основанные на удаленном, также удаляются, как показано в примере ниже:

DROP VIEW view_Consultant;

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

results matching ««

    No results matching ««

    Это тоже интересно:

  • Инструкция create table и ограничения декларативной целостности
  • Инструкция crane 167 на русском
  • Инструкция brother kr 830 на русском
  • Инструкция coinkeeper по мобильному приложению
  • Инструкция braun texstyle 7 4690

  • Понравилась статья? Поделить с друзьями:
    0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии