Need help with creating a view
-
I'm sending data to a closed, vendor application using views. We have one field (role) that represents the role the user plays. The vendor application exposes three of these roles but only as booleans. This is beyond my SQL skill level....how to I take this one field and return three boolean fields out of it? Thanks, Michael
-
I'm sending data to a closed, vendor application using views. We have one field (role) that represents the role the user plays. The vendor application exposes three of these roles but only as booleans. This is beyond my SQL skill level....how to I take this one field and return three boolean fields out of it? Thanks, Michael
create view view_name as select field_name as f1, field_name as f2, field_name as f3 from table_namethis will select a column a 3 duplicated columnsDie Energie der Welt ist konstant. Die Entropie der Welt strebt einem Maximum zu.
-
I'm sending data to a closed, vendor application using views. We have one field (role) that represents the role the user plays. The vendor application exposes three of these roles but only as booleans. This is beyond my SQL skill level....how to I take this one field and return three boolean fields out of it? Thanks, Michael
As Dmitry suggested, select the column 3 times. You can also incorporate a case statement to send a 1 or 0 as the value.
SELECT
CASE WHEN FieldName = 1 THEN 1 ELSE 0 END F1,
CASE WHEN FieldName = 2 THEN 1 ELSE 0 END F2,
CASE WHEN FieldName = 3 THEN 1 ELSE 0 END F3
FROM TableNameNever underestimate the power of human stupidity RAH
-
As Dmitry suggested, select the column 3 times. You can also incorporate a case statement to send a 1 or 0 as the value.
SELECT
CASE WHEN FieldName = 1 THEN 1 ELSE 0 END F1,
CASE WHEN FieldName = 2 THEN 1 ELSE 0 END F2,
CASE WHEN FieldName = 3 THEN 1 ELSE 0 END F3
FROM TableNameNever underestimate the power of human stupidity RAH
Looks like your answer gives me the answer I need. CASE WHEN Role = 1 Then 1 else 0 end IsAdministrator, CASE WHEN Role = 2 Then 1 else 0 end IsDecisionMaker, etc. Thanks, Michael
-
As Dmitry suggested, select the column 3 times. You can also incorporate a case statement to send a 1 or 0 as the value.
SELECT
CASE WHEN FieldName = 1 THEN 1 ELSE 0 END F1,
CASE WHEN FieldName = 2 THEN 1 ELSE 0 END F2,
CASE WHEN FieldName = 3 THEN 1 ELSE 0 END F3
FROM TableNameNever underestimate the power of human stupidity RAH
Dimitry -- I now have to add one additional field but this one is harder. Each contact has an AccessGranted table tied to them with the locations they can access. I need to reduce that table down to a boolean CanAccess. How do I do something like that when I don't really have a customer key to work with. This is what my view looks like right now:
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