View: dbo.Custom_Value_View | |||
View definition | |||
-- has fields added that are of the right type for what's in custom_value. -- like the text "2009-08-14" will get converted to the date 2009-08-14 00:00:00 -- in the custom_value_as_date field if the type of the Custom_Field associated -- with the Custom_Value is 2 (Date). create view dbo.Custom_Value_View as select CV.custom_value_KEY , CV.custom_field_KEY , CF.description as custom_field__description , CF.custom_field_type_KEY as custom_field__custom_field_type_KEY , CF.owner_table_name as custom_field__owner_table_name , CV.row_KEY , CV.custom_value , CV.custom_grouping_KEY , case when CF.custom_field_type_KEY = 4 and CV.custom_value = 'True' then N'Yes' collate Latin1_General_CI_AS when CF.custom_field_type_KEY = 4 then N'No' collate Latin1_General_CI_AS else null end as custom_value_as_checkbox , case when isnumeric(CV.custom_value) = 1 and CF.custom_field_type_KEY = 3 then convert(decimal(17, 2), CV.custom_value) else null end as custom_value_as_number , case when CF.custom_field_type_KEY = 2 -- dates then case -- Make sure it's really close to the smalldatetime form we expect, -- which is is yyyy-mm-dd. -- This pattern specifies a date component only, and a range that's near the -- limits of smalldatetime. -- This sanity test probably isn't necessary. It's ispired by the original one -- that was in place before we had constraints that prevented bad dates from -- being saved. when custom_value like N'[12][09][0-9][0-9]-[01][0-9]-[0-3][0-9]' then case -- It's very much like a smalldatetime. Check the value min/max limits. when cast(custom_value as date) >= cast('1900-01-01' as date) and cast(custom_value as date) <= cast('2079-06-06' as date) then convert(smalldatetime, custom_value, 121) else null end else null end else null end as custom_value_as_date , case when isnumeric(CV.custom_value) = 1 and CF.custom_field_type_KEY = 6 then convert(int, CV.custom_value) else null end as custom_value__staff_KEY , custom_grouping_KEY as custom_value__custom_grouping_KEY from dbo.Custom_Value as CV left outer join dbo.Custom_Field as CF on CF.custom_field_KEY = CV.custom_field_KEY | |||