Difference between revisions of "Sales.vStoreWithDemographics (view)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Sales.vStoreWithDemographics |- |} <pre> CREATE VIEW [Sales].[vStoreWithDemogr...)
 
Line 69: Line 69:
 
     FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
 
     FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
 
</pre>
 
</pre>
 +
 +
== automatically generated ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|-
 +
| '''view
 +
| Sales.vStoreWithDemographics
 +
|-
 +
 +
| '''Description
 +
| Stores (names and addresses) that sell Adventure Works Cycles products to consumers.
 +
|-
 +
 +
|}
 +
 +
<pre>
 +
 +
CREATE VIEW [Sales].[vStoreWithDemographics] AS
 +
SELECT
 +
    s.[CustomerID]
 +
    ,s.[Name]
 +
    ,ct.[Name] AS [ContactType]
 +
    ,c.[Title]
 +
    ,c.[FirstName]
 +
    ,c.[MiddleName]
 +
    ,c.[LastName]
 +
    ,c.[Suffix]
 +
    ,c.[Phone]
 +
    ,c.[EmailAddress]
 +
    ,c.[EmailPromotion]
 +
    ,at.[Name] AS [AddressType]
 +
    ,a.[AddressLine1]
 +
    ,a.[AddressLine2]
 +
    ,a.[City]
 +
    ,sp.[Name] AS [StateProvinceName]
 +
    ,a.[PostalCode]
 +
    ,cr.[Name] AS [CountryRegionName]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet]
 +
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 +
        (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees]
 +
FROM [Sales].[Store] s
 +
    INNER JOIN [Sales].[StoreContact] sc
 +
    ON sc.[CustomerID] = s.[CustomerID]
 +
    INNER JOIN [Person].[Contact] c
 +
    ON c.[ContactID] = sc.[ContactID]
 +
    INNER JOIN [Person].[ContactType] ct
 +
    ON sc.[ContactTypeID] = ct.[ContactTypeID]
 +
    INNER JOIN [Sales].[CustomerAddress] ca
 +
    ON ca.[CustomerID] = s.[CustomerID]
 +
    INNER JOIN [Person].[Address] a
 +
    ON a.[AddressID] = ca.[AddressID]
 +
    INNER JOIN [Person].[StateProvince] sp
 +
    ON sp.[StateProvinceID] = a.[StateProvinceID]
 +
    INNER JOIN [Person].[CountryRegion] cr
 +
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
 +
    INNER JOIN [Person].[AddressType] at
 +
    ON ca.[AddressTypeID] = at.[AddressTypeID]
 +
WHERE s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]
 +
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
 +
</pre>
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Referenced Object
 +
| '''Object Type
 +
| '''Dependency Type
 +
 +
|-
 +
| [[Person.Address_(table)|Person.Address]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.AddressType_(table)|Person.AddressType]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.ContactType_(table)|Person.ContactType]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.StateProvince_(table)|Person.StateProvince]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Sales.Customer_(table)|Sales.Customer]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Sales.CustomerAddress_(table)|Sales.CustomerAddress]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Sales.Store_(table)|Sales.Store]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Sales.StoreContact_(table)|Sales.StoreContact]]
 +
| Table
 +
| Select
 +
|}

Revision as of 23:59, 26 November 2009

wikibot

view Sales.vStoreWithDemographics 
CREATE VIEW [Sales].[vStoreWithDemographics] AS 
SELECT 
    s.[CustomerID] 
    ,s.[Name] 
    ,ct.[Name] AS [ContactType] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,c.[Phone] 
    ,c.[EmailAddress] 
    ,c.[EmailPromotion] 
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees] 
FROM [Sales].[Store] s
    INNER JOIN [Sales].[StoreContact] sc 
    ON sc.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = sc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON sc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');

automatically generated

view Sales.vStoreWithDemographics
Description Stores (names and addresses) that sell Adventure Works Cycles products to consumers. 

CREATE VIEW [Sales].[vStoreWithDemographics] AS 
SELECT 
    s.[CustomerID] 
    ,s.[Name] 
    ,ct.[Name] AS [ContactType] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,c.[Phone] 
    ,c.[EmailAddress] 
    ,c.[EmailPromotion] 
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees] 
FROM [Sales].[Store] s
    INNER JOIN [Sales].[StoreContact] sc 
    ON sc.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = sc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON sc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');


Referenced Object Object Type Dependency Type
Person.Address Table Select
Person.AddressType Table Select
Person.Contact Table Select
Person.ContactType Table Select
Person.CountryRegion Table Select
Person.StateProvince Table Select
Sales.Customer Table Select
Sales.CustomerAddress Table Select
Sales.Store Table Select
Sales.StoreContact Table Select
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.vStoreWithDemographics_(view)&oldid=1294"