cust_CCIW_v_person_attribute

USE [ArenaDB]
GO

/****** Object:  View [dbo].[cust_CCIW_v_person_attribute]    Script Date: 3/2/2022 9:49:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



/*
Attribute type values
<option value="0">Int</option>
                <option value="1">String</option>
                <option value="2">DateTime</option>
                <option selected="selected" value="3">Lookup</option>
                <option value="4">YesNo</option>
                <option value="5">Decimal</option>
                <option value="6">Currency</option>
                <option value="7">Url</option>
                <option value="8">Guid</option>
                <option value="9">Document</option>
                */

CREATE VIEW [dbo].[cust_CCIW_v_person_attribute]
AS
     SELECT CASE a.attribute_type
                WHEN 0
                THEN CONVERT(VARCHAR(50),int_value)
                WHEN 1
                THEN varchar_value
                WHEN 2
                THEN CONVERT(VARCHAR(10), datetime_value, 101)
                WHEN 3
                THEN
     (
         SELECT l.lookup_value
         FROM core_lookup l
         WHERE l.lookup_id = pa.int_value
     )
                WHEN 4
                THEN CASE int_value
                         WHEN 1
                         THEN 'Yes'
                         ELSE 'No'
                     END
                WHEN 5
                THEN CAST(decimal_value AS VARCHAR)
                WHEN 6
                THEN CAST(decimal_value AS VARCHAR)
                WHEN 7
                THEN varchar_value
                WHEN 8
                THEN varchar_value
                WHEN 9
                THEN CONVERT(VARCHAR(10),int_value)
            END AS attribute_value,
            a.attribute_id,
            a.attribute_group_id,
            a.attribute_name,
            a.attribute_type,
            pa.person_id,
            pa.int_value,
            pa.varchar_value,
            pa.datetime_value,
            pa.decimal_value,
            pa.date_created,
            pa.date_modified,
            pa.created_by,
            pa.modified_by,
            pa.organization_id
     FROM dbo.core_attribute AS a
          INNER JOIN dbo.core_person_attribute AS pa ON a.attribute_id = pa.attribute_id
                                                        AND a.organization_id = pa.organization_id;
GO