Need last bit of help with a view being built
-
I have to construct a view to feed a 3rd party application. On the application database they have a boolean field CanAccess which is a blanket item for access to any location. On the master database where I'm building my view, the contact has a LocationAccess table containing access rights for each explicite location. What I need in my view is a way of returning something like Count(select * from LocationAccess) > 0 for each contact I'm running through. Here is what I've got so far. If anyone can suggest how I add this last item I'd greatly appreciate it! (BTW - yes the orderby is required and thus the TOP 100% is also required)
SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey, CASE WHEN dbo.ContactMaster.Role = 1 THEN 1 ELSE 0 END AS IsAdministrator,
CASE WHEN dbo.ContactMaster.Role = 6 THEN 1 ELSE 0 END AS IsDecisionMaker,
CASE WHEN dbo.ContactMaster.Role = 5 THEN 1 ELSE 0 END AS IsAccountingAP
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey -
I have to construct a view to feed a 3rd party application. On the application database they have a boolean field CanAccess which is a blanket item for access to any location. On the master database where I'm building my view, the contact has a LocationAccess table containing access rights for each explicite location. What I need in my view is a way of returning something like Count(select * from LocationAccess) > 0 for each contact I'm running through. Here is what I've got so far. If anyone can suggest how I add this last item I'd greatly appreciate it! (BTW - yes the orderby is required and thus the TOP 100% is also required)
SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey, CASE WHEN dbo.ContactMaster.Role = 1 THEN 1 ELSE 0 END AS IsAdministrator,
CASE WHEN dbo.ContactMaster.Role = 6 THEN 1 ELSE 0 END AS IsDecisionMaker,
CASE WHEN dbo.ContactMaster.Role = 5 THEN 1 ELSE 0 END AS IsAccountingAP
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKeyOptions to tackle this sub query (select count(*) from loaction....) - not recommended as it can destroy a server. Use a join to the location table and a group by your existing fields - just plain ugly Create another view that counts the location info and then use a join just like any other table/view - probably the way I would go create a UDF that does the same as the view - can you use a UDF in a view?
Never underestimate the power of human stupidity RAH
-
Options to tackle this sub query (select count(*) from loaction....) - not recommended as it can destroy a server. Use a join to the location table and a group by your existing fields - just plain ugly Create another view that counts the location info and then use a join just like any other table/view - probably the way I would go create a UDF that does the same as the view - can you use a UDF in a view?
Never underestimate the power of human stupidity RAH
OK -- so let's say I create a view that gives me ContactIdentifier and Count. So when my view is running how do I specify that other view as input to the query. Do I do a join between the contact table and that view?
-
OK -- so let's say I create a view that gives me ContactIdentifier and Count. So when my view is running how do I specify that other view as input to the query. Do I do a join between the contact table and that view?
Assuming that every contact will have 1 or more counted record then use an inner join from your existing view to the count view and this will filter form the filter. If not then do a left join.
Never underestimate the power of human stupidity RAH