News

Copyright © 2008-2018 Paula DiTallo

Tag Cloud



How do I Delete a View? How do I Create a View?

Before I create views, I generally work out what I want to retrieve
in my SELECT statement ahead of time so I'll just  have to cut and  paste the query. The example below is done  in T-SQL/Sybase format, however for Oracle and MySQL, just  place a semi-colon ';' at the end of your statement and remove the  'GO' command.
        

To drop (delete) an existing view:

DROP VIEW vw_rpt_metroBestCustomers
GO

To create a view:

CREATE VIEW vw_rpt_metroBestCustomers
( CustomerName,
   OfficeNum,
   City,
   StateOrProv,
   Country,
   ZipCode
  )
AS
SELECT a.FirstName + ', ' + a.LastName,
          b.OfficePhoneNum,
          c.City,
          c.StateOrProvAbbr,
          c.Country,
          c.PostalCode
    FROM Customer a,
         CustLocAssoc x,
         CustContactAssoc y,
         Location c,
         Contact b
    WHERE a.CustID = x.CustID
      AND a.CustID = y.CustID
      AND y.ContactID = b.ContactID
      AND x.LocID = c.LocID
      AND a.LoyaltyMedian > 85.5

GO
      

I frequently rename columns when developing views to make
it easier for simple, text-based reporting--however, renaming
the columns isn't necessary.

The create view statement above could have been written
as follows:


CREATE VIEW vw_rpt_metroBestCustomers
AS
(SELECT a.FirstName + ', ' + a.LastName,
b.OfficePhoneNum,
c.City,
c.StateOrProvAbbr,
c.Country,
c.PostalCode
FROM Customer a,
CustLocAssoc x,
CustContactAssoc y,
Location c,
Contact b
WHERE a.CustID = x.CustID
AND a.CustID = y.CustID
AND y.ContactID = b.ContactID
AND x.LocID = c.LocID
AND a.LoyaltyMedian > 85.5
)
GO

Tuesday, February 22, 2011 5:01 PM

Feedback

No comments posted yet.


Post A Comment
Title:
Name:
Email:
Comment:
Verification: