Combine two Oracle SQL queries with one LEFT OUTER JOIN and two INNER JOINS from four tables

Question

I have two sql queries in Oracle that I would like to combine. The first query returns domain descriptions from a table and the second query will return only the domain descriptions that exist within the input parameter(COST_CENTER).

1st query:

SELECT distinct condition as condition_code, value as condition_literal
FROM CULVERT_INSPECT
   LEFT OUTER JOIN
   (SELECT EXTRACTVALUE (CodedValues.COLUMN_VALUE, 'CodedValue/Code')
              AS Code,
           EXTRACTVALUE (CodedValues.COLUMN_VALUE, 'CodedValue/Name')
              AS VALUE
      FROM GDB_ITEMS_VW items
           INNER JOIN 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 = 'OVERALL_CONDITION') coded_values
             on CULVERT_INSPECT.condition = coded_values.code
 ORDER BY CONDITION_CODE

2nd Query:

SELECT distinct CULVERT_INSPECT.CONDITION
FROM CULVERTS
   INNER JOIN CULVERT_INSPECT ON (CULVERTS.GLOBALID_1 = 
CULVERTGID)
WHERE COST_CENTER = '551807'

The output from first query:

CONDITION_CODE  CONDITION_LITERAL
0               Excellent - Like New
1               Good - Some Wear, Structurally Sound
2               Poor - Deteriorated, Consider For Repair
3               Very Poor - Serious Deterioration
4               Inaccessible

Answer 1

Try this using a with clause. It is untested though as there is no sample data.

WITH cons
         AS (SELECT DISTINCT culvertgid , condition AS condition_code,
                             value     AS condition_literal
             FROM   culvert_inspect
                    LEFT OUTER JOIN (SELECT Extractvalue (CodedValues.column_value,
                                            'CodedValue/Code') AS Code,
                                            Extractvalue (CodedValues.column_value,
                                            'CodedValue/Name') AS VALUE
                                     FROM   gdb_items_vw items
                                            inner join 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 = 'OVERALL_CONDITION')
                    coded_values
                 ON culvert_inspect.condition = coded_values.code)
    SELECT DISTINCT ci.condition_code
    FROM   culverts c
           INNER JOIN  cons ci
                   ON ( c.globalid_1 = ci.culvertgid )
    WHERE  cost_center = '551807'
    ORDER  BY condition_code;