UFN_EDUCATIONALINVOLVEMENT_GETLIST

In-line table-value function that returns list of educational involvements, with descriptions rather than IDs

Return

Return Type
table

Definition

Copy



CREATE function dbo.UFN_EDUCATIONALINVOLVEMENT_GETLIST()
returns table
as return
    select
        INVOLVEMENT.CONSTITUENTID,
        INVOLVEMENT.ID,
        dbo.UFN_EDUCATIONALINVOLVEMENTTYPECODE_GETDESCRIPTION(INVOLVEMENT.EDUCATIONALINVOLVEMENTTYPECODEID) as EDUCATIONALINVOLVEMENTTYPECODE,
        EDUCATIONALINVOLVEMENTNAME.NAME,
        INVOLVEMENT.POSITION,
        INVOLVEMENT.DATEFROM,
        INVOLVEMENT.DATETO,
        INVOLVEMENT.COMMENT,
        INSTITUTION.NAME as INSTITUTION,
        dbo.UFN_EDUCATIONINVOLVEMENTAWARDCODE_GETDESCRIPTION(INVOLVEMENT.EDUCATIONALINVOLVEMENTAWARDCODEID) as EDUCATIONALINVOLVEMENTAWARDCODE,
        case 
            when INVOLVEMENT.ACADEMICCATALOGDEPARTMENTID is not null then CATALOGDEPT.NAME
            when INVOLVEMENT.EDUCATIONALDEPARTMENTCODEID is not null then dbo.UFN_EDUCATIONALDEPARTMENTCODE_GETDESCRIPTION(INVOLVEMENT.EDUCATIONALDEPARTMENTCODEID)
            else ''
            end 
            as DEPARTMENT
    from
        dbo.EDUCATIONALINVOLVEMENT as INVOLVEMENT
        left join dbo.EDUCATIONALINSTITUTION as INSTITUTION on INVOLVEMENT.EDUCATIONALINSTITUTIONID = INSTITUTION.ID
        left join dbo.EDUCATIONALINVOLVEMENTNAME on INVOLVEMENT.EDUCATIONALINVOLVEMENTNAMEID = EDUCATIONALINVOLVEMENTNAME.ID
        left join dbo.ACADEMICCATALOGDEPARTMENT as CATALOGDEPT on INVOLVEMENT.ACADEMICCATALOGDEPARTMENTID = CATALOGDEPT.ID