CREATE VIEW must be the only expression in the party

Question

I'm trying to pretend. 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; 

Sorry, I get an error in the line containing CREATE VIEW showing

"CREATE VIEW must be the only expression in the package

How can I fix this ?!

Answer 1

Just as the error says, the CREATE VIEW statement must be the only statement in the query package.

You have two options in this scenario, depending on the functionality you want to achieve:

• Put the CREATE VIEW request 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 CTE and before CREATE VIEW request

  - Option number 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;