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.).