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?!
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:
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
)
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;