Search code examples
sqlsql-serverddlcreate-view

CREATE VIEW must be the only statement in the batch


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?!


Solution

  • 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;