SQL Scripting Creating a view instead of derived table

Question

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'.

The second part comes up with errors for me: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '‘'. Msg 111, Level 15, State 1, Line 6 'CREATE VIEW' must be the first statement in a query batch. Msg 156, Level 15, State 1, Line 9 Incorrect syntax near the keyword 'Create'. Msg 111, Level 15, State 1, Line 9 'CREATE VIEW' must be the first statement in a query batch. Msg 102, Level 15, State 1, Line 12 Incorrect syntax near ')'. Though I have been working on this so long I could be doing something wrong. Is this correct or where am I messing up? — Daniel Erb
– Daniel Erb, Commented Aug 18, 2013 at 18:59
You did not specified view name in create, it should be create view [EarliestInvoiceandTotalVEW] as --etc. — i-one
– i-one, Commented Aug 18, 2013 at 19:07

Andomar · Accepted Answer · 2013-08-18 19:16:29Z

1

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 ...')

answered Aug 18, 2013 at 19:16

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-08-18 19:17:34Z

1

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).

answered Aug 18, 2013 at 19:17

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Daniel Erb Over a year ago

Thanks for steering me in the right direction. Ultimately this is what I got to work (didn't get the order by to work (yet?)) I will edit my post with what I got to work.

Gordon Linoff Over a year ago

@DanielErb . . . order bys in views are not guaranteed to have any effect unless you are using top in the view itself.

Collectives™ on Stack Overflow

SQL Scripting Creating a view instead of derived table

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related