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 makeit easier for simple, text-based reporting--however, renamingthe columns isn't necessary.The create view statement above could have been writtenas follows:CREATE VIEW vw_rpt_metroBestCustomersAS (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
