将两个Oracle SQL查询与四个表中的一个LEFT OUTER JOIN和两个INNER JOINS组合起来

Question

我在Oracle中有两个我想合并的SQL查询。第一个查询从表中返回域描述，第二个查询将仅返回输入参数（COST_CENTER）中存在的域描述。

第一次查询：

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 

第二查询：

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

从第一查询的输出：

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

此使用with clause尝试。由于没有样本数据，所以未经测试。

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;