Difference between revisions of "Purchasing.vVendor (view)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Purchasing.vVendor |- |} <pre> CREATE VIEW [Purchasing].[vVendor] AS SELECT ...)
 
Line 44: Line 44:
 
     ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
 
     ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
 
</pre>
 
</pre>
 +
 +
== automatically generated ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|-
 +
| '''view
 +
| Purchasing.vVendor
 +
|-
 +
 +
| '''Description
 +
| Vendor (company) names and addresses and the names of vendor employees to contact.
 +
|-
 +
 +
|}
 +
 +
<pre>
 +
 +
CREATE VIEW [Purchasing].[vVendor] AS
 +
SELECT
 +
    v.[VendorID]
 +
    ,v.[Name]
 +
    ,ct.[Name] AS [ContactType]
 +
    ,c.[Title]
 +
    ,c.[FirstName]
 +
    ,c.[MiddleName]
 +
    ,c.[LastName]
 +
    ,c.[Suffix]
 +
    ,c.[Phone]
 +
    ,c.[EmailAddress]
 +
    ,c.[EmailPromotion]
 +
    ,a.[AddressLine1]
 +
    ,a.[AddressLine2]
 +
    ,a.[City]
 +
    ,[StateProvinceName] = sp.[Name]
 +
    ,a.[PostalCode]
 +
    ,[CountryRegionName] = cr.[Name]
 +
FROM [Purchasing].[Vendor] v
 +
    INNER JOIN [Purchasing].[VendorContact] vc
 +
    ON vc.[VendorID] = v.[VendorID]
 +
    INNER JOIN [Person].[Contact] c
 +
    ON c.[ContactID] = vc.[ContactID]
 +
    INNER JOIN [Person].[ContactType] ct
 +
    ON vc.[ContactTypeID] = ct.[ContactTypeID]
 +
    INNER JOIN [Purchasing].[VendorAddress] va
 +
    ON va.[VendorID] = v.[VendorID]
 +
    INNER JOIN [Person].[Address] a
 +
    ON a.[AddressID] = va.[AddressID]
 +
    INNER JOIN [Person].[StateProvince] sp
 +
    ON sp.[StateProvinceID] = a.[StateProvinceID]
 +
    INNER JOIN [Person].[CountryRegion] cr
 +
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
 +
</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.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
 +
|-
 +
| [[Purchasing.Vendor_(table)|Purchasing.Vendor]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Purchasing.VendorAddress_(table)|Purchasing.VendorAddress]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Purchasing.VendorContact_(table)|Purchasing.VendorContact]]
 +
| Table
 +
| Select
 +
|}

Revision as of 00:59, 27 November 2009

wikibot

view Purchasing.vVendor 
CREATE VIEW [Purchasing].[vVendor] AS 
SELECT 
    v.[VendorID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
FROM [Purchasing].[Vendor] v
    INNER JOIN [Purchasing].[VendorContact] vc 
    ON vc.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = vc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON vc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Purchasing].[VendorAddress] va 
    ON va.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = va.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

automatically generated

view Purchasing.vVendor
Description Vendor (company) names and addresses and the names of vendor employees to contact. 

CREATE VIEW [Purchasing].[vVendor] AS 
SELECT 
    v.[VendorID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
FROM [Purchasing].[Vendor] v
    INNER JOIN [Purchasing].[VendorContact] vc 
    ON vc.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = vc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON vc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Purchasing].[VendorAddress] va 
    ON va.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = va.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];


Referenced Object Object Type Dependency Type
Person.Address Table Select
Person.Contact Table Select
Person.ContactType Table Select
Person.CountryRegion Table Select
Person.StateProvince Table Select
Purchasing.Vendor Table Select
Purchasing.VendorAddress Table Select
Purchasing.VendorContact Table Select
Retrieved from "http://dbshelp.devio.at/index.php?title=Purchasing.vVendor_(view)&oldid=1295"