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