将两个 Oracle SQL 查询与四个表中的一个左外联接和两个内联接组合在一起

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;