Get domain descriptions for multiple fields using SQL

Question

How can I get domain descriptions for multiple fields using SQL?

I have successfully used the Oracle SQL in this ESRI document: Resolving domain codes to description values using SQL. But how do I do this for multiple fields?

SELECT
    EVENT_ID,
    ROAD_ID,
    Event_Status_Description
FROM 
    TEMP_OWNER.ROADS
    LEFT OUTER JOIN
        (SELECT
           EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code') AS Event_Status_Code,
           EXTRACTVALUE(CodedValues.COLUMN_VALUE,'CodedValue/Name') AS Event_Status_Description
        FROM
           SDE.GDB_ITEMS_VW items 
           INNER JOIN SDE.GDB_ITEMTYPES itemtypes 
           ON items.Type = itemtypes.UUID,
           TABLE(XMLSEQUENCE(XMLType(Definition).Extract ('/GPCodedValueDomain2/CodedValues/CodedValue')))     CodedValues
        WHERE itemtypes.Name = 'Coded Value Domain' 
           AND items.Name = 'EVENT_STATUS') CodedValues
           ON TEMP_OWNER.ROADS.EVENT_STATUS = CodedValues.Event_Status_Code

Answer 1

The answer was quite simple: do additional joins.

SELECT
    EVENT_ID,
    ROAD_ID,
    Event_Status_Description,
    Strategy_Description,
    Activity_Description
FROM 
    ((TEMP_OWNER.ROADS
    LEFT OUTER JOIN
   (SELECT
     EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code') AS Event_Status_Code,
     EXTRACTVALUE(CodedValues.COLUMN_VALUE,'CodedValue/Name') AS Event_Status_Description
   FROM
     SDE.GDB_ITEMS_VW items 
     INNER JOIN SDE.GDB_ITEMTYPES itemtypes 
     ON items.Type = itemtypes.UUID,
     TABLE(XMLSEQUENCE(XMLType(Definition).Extract  ('/GPCodedValueDomain2/CodedValues/CodedValue')))     CodedValues
    WHERE itemtypes.Name = 'Coded Value Domain' 
     AND items.Name = 'EVENT_STATUS') CodedValues
     ON TEMP_OWNER.ROADS.EVENT_STATUS = CodedValues.Event_Status_Code)
    LEFT OUTER JOIN
   (SELECT
     EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code') AS Strategy_Code,
     EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Name') AS Strategy_Description
   FROM
     SDE.GDB_ITEMS_VW items 
     INNER JOIN SDE.GDB_ITEMTYPES itemtypes 
     ON items.Type = itemtypes.UUID,
     TABLE(XMLSEQUENCE(XMLType(Definition).Extract  ('/GPCodedValueDomain2/CodedValues/CodedValue')))     CodedValues
    WHERE itemtypes.Name = 'Coded Value Domain' 
     AND items.Name = 'STRATEGY') CodedValues
     ON TEMP_OWNER.ROADS.STRATEGY = CodedValues.Strategy_Code)
    LEFT OUTER JOIN
   (SELECT
     EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code') AS Activity_Code,
     EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Name') AS Activity_Description
   FROM
     SDE.GDB_ITEMS_VW items 
     INNER JOIN SDE.GDB_ITEMTYPES itemtypes 
     ON items.Type = itemtypes.UUID,
     TABLE(XMLSEQUENCE(XMLType(Definition).Extract  ('/GPCodedValueDomain2/CodedValues/CodedValue')))     CodedValues
    WHERE itemtypes.Name = 'Coded Value Domain' 
     AND items.Name = 'ACTIVITY_RD') CodedValues
     ON TEMP_OWNER.ROADS.ACTIVITY = CodedValues.Activity_Code

It helped to mock this up, with domains converted to dummy tables, in the graphical query builder in MS Access, and then look at the SQL to see how joins ought to be structured (syntax, brackets, etc.).