Skip to main content
pdf?stylesheet=default
Blackboard Help

Synonyms for User Defined Attribute (UDA) EXT Tables

The ODS also includes synonyms for the _ext tables that hold User Defined Attributes (UDAs) in the BBLEARN schema/database. The names of these synonyms begin with ODS_ and end with _EXT_SYN. These synonyms will allow users to include UDAs in their custom reports. To do this users should join the relevant synonym to the ODS views in their queries. The following table lists each synonym, the BBLEARN table that it references, and the names of the ODS View and synonym columns that should be used to join the view to the synonym.

Synonym Information
ODS Synonym BBLEARN Table View Join Column Synonym Join Column
ODS_II_EXT_SYN CLP_ASSESSMENT_
INITIATIVE_EXT
II_PK1 CLP_ASSESSMENT_
INITIATIVE_PK1
ODS_IP_EXT_SYN CLP_ASSESSMENT_
PROJECT_EXT
IP_PK1 CLP_ASSESSMENT_
PROJECT_PK1
ODS_PERSON_EXT_SYN CLP_PERSON_EXT PERSON_PK1 CLP_PERSON_PK1
ODS_PROGRAM_EXT_SYN CLP_PROGRAM_EXT PROGRAM_PK1 CLP_PROGRAM_PK1
ODS_SECTION_EXT_SYN CLP_SECTION_EXT COURSE_SECTION_PK1 CLP_SECTION_PK1
ODS_EE_EXT_SYN CLP_EE_EXT EE_PK1 CLP_EE_PK1
ODS_EE_SECTION_EXT_SYN CLP_EE_SECTION_EXT EE_SECTION_PK1 CLP_EE_SECTION_PK1
ODS_COURSE_EXT_SYN CLP_COURSE_EXT COURSE_PK1 CLP_COURSE_PK1
ODS_OU_EXT_SYN CLP_OPERATING_
UNIT_EXT
OU_PK1 CLP_OPERATING_UNIT_PK1
ODS_STD_CATEGORY_SOG_
EXT_SYN
CLP_STANDARD_SOG_EXT SOG_PK1 CLP_STANDARD_
SOG_PK1
ODS_ IPO _SOG_EXT_SYN CLP_AP_SOG_EXT SOG_PK1 CLP_AP_SOG_PK1
ODS_ COURSE_OBJ_ SOG_EXT_SYN CLP_COURSE_SOG_EXT SOG_PK1 CLP_COURSE_SOG_PK1
ODS_EE_OBJ_SOG_EXT_SYN CLP_EE_SOG_EXT SOG_PK1 CLP_EE_SOG_PK1
ODS_OU_GOAL_SOG_EXT_SYN CLP_OPERATING_UNIT_
SOG_EXT
SOG_PK1 CLP_OPERATING_
UNIT_SOG_PK1
ODS_PROGRAM_GOAL_EXT_SYN CLP_PROGRAM_SOG_EXT SOG_PK1 CLP_PROGRAM_
SOG_PK1
ODS_ENUM_ATTR_VALUE_
EXT_SYN
ENUM_ATTRIBUTE_VALUE * PK1

* The last synonym (ODS_ENUM_ATR_VALUE_EXT_SYN) is a little different.  The table referred to by this synonym contains the displayed values for selection attribute UDAs (short or medium).  In the case of selection attribute UDAs the _ext table doesn't contain the displayed value.  Instead it contains a numeric foreign key to the table referred to by the ODS_ENUM_ATRR_VALUE_EXT_SYN synonym.  So to get the display value for a selection attribute UDA you should join the ODS_ENUM_ATR_VALUE_EXT_SYN into your query matching the ODS_ENUM_ATR_VALUE_EXT_SYN.pk1 to the UDA column value. The following is a sample query.

Sample Query

The following query lists all sections from ods_section_dim_vw and also retrieves the display values for two selection type section UDAs - odo_selsstr# (selection attribute: short string) and odo_selmstr# (selection attribute: medium string).

select s.level_1_ou_title, s.COURSE_EE_TITLE, s.section_title,

                    seshort.label Selection_Short_String,

                    semed.label Selection_Medium_String

from ods_section_dim_vw s

                    INNER JOIN ODS_COURSE_SECTION_EXT_SYN se ON se.clp_section_pk1 = s.course_section_pk1

                    LEFT OUTER JOIN ODS_ENUM_ATTR_VALUE_EXT_SYN seshort ON se.odo_selsstr# = seshort.pk1

                    LEFT OUTER JOIN ODS_ENUM_ATTR_VALUE_EXT_SYN semed ON se.odo_selmstr# = semed.pk1

order by s.level_1_ou_title, s.COURSE_EE_TITLE, s.section_title