CODEWITHSUNDEEP
sql-server
Daniel Erb
Daniel Erb

Reputation: 61

SQL Scripting Creating a view instead of derived table

So This is a school question similar to another I was given a code:

USE AP

SELECT VendorName, FirstInvoiceDate, InvoiceTotal
FROM Invoices JOIN
  (SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
   FROM Invoices
   GROUP BY VendorID) AS FirstInvoice
  ON (Invoices.VendorID = FirstInvoice.VendorID AND
      Invoices.InvoiceDate = FirstInvoice.FirstInvoiceDate)
JOIN Vendors
  ON Invoices.VendorID = Vendors.VendorID
ORDER BY VendorName, FirstInvoiceDate

I need to change this to create a view instead of a derived table I was thinking something more like

IF NOT EXISTS (SELECT * FROM sys.views
        Where name = ‘EarliestInvoiceandTotalVEW’)


CREATE View AS
Select VendorName, FirstInvoiceDate, InvoiceTotal
From Invoices JOIN
    (Create VIEW FirstInvoice AS
    SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
    From Invoices
    Group By VendorID) As FirstInvoice
    ON (Invoices.VendorID = FirstInvoice.VendorID AND
      Invoices.InvoiceDate = FirstInvoice.FirstInvoiceDate)
JOIN Vendors
  ON Invoices.VendorID = Vendors.VendorID
ORDER BY VendorName, FirstInvoiceDate

This would make a view to do the same thing and check if it exists so it is not recreated/defined each time.

Thanks for any input on this!

I apologize about the format I took it directly out of SQL Server and it normally is formatted well...

This is what I got to work:

 USE AP
 Declare @Create1 varchar(8000)

IF EXISTS (SELECT * FROM sys.views Where sys.views.name = 'EarliestInvoiceandTotalVIEW')
    drop view EarliestInvoiceandTotalVIEW;

SET @CREATE1 = 'CREATE View EarliestInvoiceTotalVIEW AS 
Select VendorName, FirstInvoiceDate, InvoiceTotal
From Invoices JOIN
  (SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
   FROM Invoices
   GROUP BY VendorID) AS FirstInvoice
  ON (Invoices.VendorID = FirstInvoice.VendorID AND
      Invoices.InvoiceDate = FirstInvoice.FirstInvoiceDate)
JOIN Vendors
  ON Invoices.VendorID = Vendors.VendorID'

Exec (@CREATE1)

I had to set the where to sys.views.name =... as well as set an exec command so that the create view is running 'first'.

Upvotes: 0

Views: 419

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

There are multiple ways to check if a view exists. However, often the approach is to delete a view if it exists and then recreate it:

IF EXISTS (SELECT * FROM sys.views Where name = ‘EarliestInvoiceandTotalVEW’)
    drop view EarliestInvoiceandTotalVEW;

Then, your create view statement has two flaws. First it is lacking a name. Second it has an embedded create view in it. You have to decide which one you want to create -- a view for the subquery or a view for the entire statement (it is not clear to me from the question which is the right approach).

Upvotes: 1

Andomar
Andomar

Reputation: 238058

In response to your comment, be sure to surround your view definition with GO statements:

if exists (select * from sys.views where name = 'ThreeDigitsOfPi')
    drop view dbo.ThreeDigitsOfPi
GO
create view dbo.ThreeDigitsOfPi 
as
select  3.14 as PI
GO

Some other points:

  • You should use single quotes ' instead of ‘ style quotes
  • Your view has to have a name (create view NameOfView as ...)
  • An view definition cannot follow an if statement, unless the view is created in dynamic SQL. For example: if not exists (...) exec('create view ...')

Upvotes: 1

Related Questions