CREATE VIEW must be the only statement in the batch

Kadaj13 picture Kadaj13 · Dec 3, 2014 · Viewed 66k times · Source

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

Answer

Radu Gheorghiu picture Radu Gheorghiu · Dec 3, 2014

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;