Kadaj13 Kadaj13 - 1 year ago 115
SQL Question

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

Answer Source

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;
    
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download