View: dbo.Report_Profile_View
View definition
CREATE VIEW dbo.Report_Profile_View
AS
SELECT
report_profile_KEY= RP.report_profile_KEY
, description = RP.description
, client_KEY = RP.client_KEY
, restrict_from_client_access = RP.restrict_from_client_access
, is_client_specific =
ISNULL(
-- The isnull call does nothing but force the column to be NOT NULL in the view
CAST(
CASE WHEN GRP_N_RP.global_report_profile_n_report_profile_KEY IS NULL
THEN 1 ELSE 0 END AS BIT),0)
, report_profile_descriptor = CASE WHEN GRP_N_RP.global_report_profile_n_report_profile_KEY IS NULL THEN N'Client' ELSE N'Firm' END
FROM
dbo.Report_Profile RP
LEFT JOIN
-- Left join because we need nulls returned for the absence of any records for the 'right' table
-- (Global_Report_Profile_N_Report_Profile) so we can determine if the given Report_Profile
-- record is a client-specific or a firm-extended Report_Profile record.
dbo.Global_Report_Profile_N_Report_Profile GRP_N_RP ON GRP_N_RP.report_profile_KEY = RP.report_profile_KEY