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

From dbscript Online Help
Jump to: navigation, search
 
(5 intermediate revisions by the same user not shown)
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
−
|-
+
|- valign="top"
−
| '''view
+
| '''View
 
| Sales.vIndividualCustomer
 
| Sales.vIndividualCustomer
−
|-
+
|- valign="top"
 +
| '''Description
 +
| Individual customers (names and addresses) that purchase Adventure Works Cycles products online.
 
|}
 
|}
  
 +
=== Source ===
 
<pre>
 
<pre>
 
CREATE VIEW [Sales].[vIndividualCustomer]  
 
CREATE VIEW [Sales].[vIndividualCustomer]  
Line 45: Line 48:
 
     FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');
 
     FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');
 
</pre>
 
</pre>
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.Address_(table)|Person.Address]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.AddressType_(table)|Person.AddressType]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.StateProvince_(table)|Person.StateProvince]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.Customer_(table)|Sales.Customer]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.CustomerAddress_(table)|Sales.CustomerAddress]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.Individual_(table)|Sales.Individual]]
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Sales_(schema)|Sales]]
 +
 +
|}
 +
 +
 
== automatically generated ==
 
== automatically generated ==
  
Line 51: Line 110:
 
| '''view
 
| '''view
 
| Sales.vIndividualCustomer
 
| Sales.vIndividualCustomer
−
|-
+
|- valign="top"
−
 
 
 
| '''Description
 
| '''Description
 
| Individual customers (names and addresses) that purchase Adventure Works Cycles products online.
 
| Individual customers (names and addresses) that purchase Adventure Works Cycles products online.
 
|-
 
|-
−
 
 
|}
 
|}
  
Line 101: Line 158:
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 
| '''Referenced Object
 
| '''Referenced Object
−
| '''Object Type
+
 
−
| '''Dependency Type
 
−
 
 
|-
 
|-
 +
| Select
 +
| Table
 
| [[Person.Address_(table)|Person.Address]]
 
| [[Person.Address_(table)|Person.Address]]
 +
 +
|-
 +
| Select
 
| Table
 
| Table
 +
| [[Person.AddressType_(table)|Person.AddressType]]
 +
 +
|-
 
| Select
 
| Select
 +
| Table
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 
|-
 
|-
−
| [[Person.AddressType_(table)|Person.AddressType]]
+
| Select
 
| Table
 
| Table
 +
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 +
 +
|-
 
| Select
 
| Select
 +
| Table
 +
| [[Person.StateProvince_(table)|Person.StateProvince]]
 +
 
|-
 
|-
−
| [[Person.Contact_(table)|Person.Contact]]
+
| Select
 
| Table
 
| Table
−
| Select
+
| [[Sales.Customer_(table)|Sales.Customer]]
 +
 
 
|-
 
|-
−
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 
−
| Table
 
 
| Select
 
| Select
−
|-
 
−
| [[Person.StateProvince_(table)|Person.StateProvince]]
 
 
| Table
 
| Table
−
| Select
+
| [[Sales.CustomerAddress_(table)|Sales.CustomerAddress]]
 +
 
 
|-
 
|-
−
| [[Sales.Customer_(table)|Sales.Customer]]
 
−
| Table
 
 
| Select
 
| Select
−
|-
 
−
| [[Sales.CustomerAddress_(table)|Sales.CustomerAddress]]
 
 
| Table
 
| Table
−
| Select
+
| [[Sales.Individual_(table)|Sales.Individual]]
 +
 
 
|-
 
|-
−
| [[Sales.Individual_(table)|Sales.Individual]]
+
| Schema
−
| Table
+
| Schema
−
| Select
+
| [[Sales_(schema)|Sales]]
 +
 
 
|}
 
|}

Latest revision as of 23:17, 23 June 2010

wikibot[edit]

View Sales.vIndividualCustomer
Description Individual customers (names and addresses) that purchase Adventure Works Cycles products online.

Source[edit]

CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    i.[CustomerID]
    ,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]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,i.[Demographics]
FROM [Sales].[Individual] i
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = i.[ContactID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = i.[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 i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');

References[edit]

Dependency Type Object Type Referenced Object
Select Table Person.Address
Select Table Person.AddressType
Select Table Person.Contact
Select Table Person.CountryRegion
Select Table Person.StateProvince
Select Table Sales.Customer
Select Table Sales.CustomerAddress
Select Table Sales.Individual
Schema Schema Sales


automatically generated[edit]

view Sales.vIndividualCustomer
Description Individual customers (names and addresses) that purchase Adventure Works Cycles products online. 

CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    i.[CustomerID]
    ,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]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,i.[Demographics]
FROM [Sales].[Individual] i
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = i.[ContactID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = i.[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 i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');


Dependency Type Object Type Referenced Object
Select Table Person.Address
Select Table Person.AddressType
Select Table Person.Contact
Select Table Person.CountryRegion
Select Table Person.StateProvince
Select Table Sales.Customer
Select Table Sales.CustomerAddress
Select Table Sales.Individual
Schema Schema Sales
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.vIndividualCustomer_(view)&oldid=2683"