How do I Delete a View? How do I Create a View?
Posted
by Paula DiTallo
on Geeks with Blogs
See other posts from Geeks with Blogs
or by Paula DiTallo
Published on Tue, 22 Feb 2011 22:01:26 GMT
Indexed on
2011/02/22
23:26 UTC
Read the original article
Hit count: 403
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
© Geeks with Blogs or respective owner
