左外部联接的标识符无效

Question

我似乎无法使用派生表获得这些左外部联接。我需要这样做，因为我需要在限定中使用子查询。

我收到的错误是ORA-00904: "E"."ENCNTR_ID": invalid identifier

我试着移动连接的位置，并反转=周围的列。有很多与此相关的帖子，但都没有给我提供答案。很多都是关于在FROM子句中用逗号分隔多个表，但我的查询没有。

SELECT
E.ENCNTR_ID
,E.REASON_FOR_VISIT
-- ,D.DIAGNOSIS_DISPLAY AS DISCHARGE_DX
-- ,D.DIAG_FTDESC AS WORKING_DX
,cclsql_cnvtdatetimeutc(E.REG_DT_TM, 2, 126) as reg_dt_tm
,cclsql_cnvtdatetimeutc(E.DISCH_DT_TM, 2, 126) as disch_dt_tm
,FACILITY.DISPLAY AS FACILITY
,P.PERSON_ID
,P.NAME_FULL_FORMATTED
,cclsql_cnvtdatetimeutc(P.BIRTH_DT_TM, 2, P.BIRTH_TZ) as birth_dt_tm
,MRN.ALIAS AS MRN
,FIN.ALIAS AS FIN
,ENC_TYPE.DISPLAY AS ENCOUNTER_TYPE
,NURSE_UNIT.DISPLAY AS NURSE_UNIT
,ROOM.DISPLAY AS ROOM
,BED.DISPLAY AS BED
,ADMITDOC_NAME.NAME_FULL_FORMATTED AS ADMITDOC_NAME
,DCDOC_NAME.NAME_FULL_FORMATTED AS DCDOC_NAME
,ADMIT_SRC.DISPLAY AS ADMIT_SOURCE
,ADMIT_TYPE.DISPLAY AS ADMIT_TYPE
,ESDP.RESULT_VAL AS ESDP
,DISCH_DISPOSITION.DISPLAY AS DISCH_DISPOSITION
FROM ENCOUNTER E
-- JOIN DISCHARGE D ON (D.ENCNTR_ID = E.ENCNTR_ID)
--  AND D.ACTIVE_IND = 1
--  AND D.END_EFFECTIVE_DT_TM > SYSDATE
JOIN PERSON P ON (P.PERSON_ID = E.PERSON_ID)
JOIN CODE_VALUE ENC_TYPE ON (ENC_TYPE.CODE_VALUE = E.ENCNTR_TYPE_CLASS_CD)
JOIN ENCNTR_ALIAS FIN ON (FIN.ENCNTR_ID = E.ENCNTR_ID)
AND FIN.ENCNTR_ALIAS_TYPE_CD = 1077 -- FIN CODE
AND FIN.ACTIVE_IND = 1
AND FIN.ACTIVE_STATUS_CD = 188
JOIN ENCNTR_ALIAS MRN ON (MRN.ENCNTR_ID = E.ENCNTR_ID)
AND MRN.ENCNTR_ALIAS_TYPE_CD = 1079 -- MRN CODE
AND MRN.ACTIVE_IND = 1
AND MRN.ACTIVE_STATUS_CD = 188
JOIN CODE_VALUE FACILITY on (FACILITY.CODE_VALUE = E.LOC_FACILITY_CD)
JOIN CODE_VALUE NURSE_UNIT on (NURSE_UNIT.CODE_VALUE = E.LOC_NURSE_UNIT_CD)
JOIN CODE_VALUE ROOM on (ROOM.CODE_VALUE = E.LOC_ROOM_CD)
JOIN CODE_VALUE BED on (BED.CODE_VALUE = E.LOC_BED_CD)
JOIN CODE_VALUE DISCH_DISPOSITION on (DISCH_DISPOSITION.CODE_VALUE = E.DISCH_DISPOSITION_CD)
JOIN PRSNL ADMITDOC_NAME ON (ADMITDOC_NAME.PERSON_ID = OA.ORDER_PROVIDER_ID)
LEFT JOIN CLINICAL_EVENT HOSP_SUM ON (HOSP_SUM.ENCNTR_ID = E.ENCNTR_ID)
AND HOSP_SUM.EVENT_CD = :p17
AND HOSP_SUM.RESULT_STATUS_CD IN (:p19, :p20, :p21)
AND HOSP_SUM.RECORD_STATUS_CD = 188 -- ACTIVE
AND HOSP_SUM.VALID_UNTIL_DT_TM > cclsql_cnvtdatetimeutc(SYSDATE, 1, 126)
LEFT JOIN PRSNL DCDOC_NAME ON (DCDOC_NAME.PERSON_ID = HOSP_SUM.VERIFIED_PRSNL_ID)
JOIN CODE_VALUE ADMIT_SRC ON (ADMIT_SRC.CODE_VALUE = E.ADMIT_SRC_CD)
JOIN CODE_VALUE ADMIT_TYPE ON (ADMIT_TYPE.CODE_VALUE = E.ADMIT_TYPE_CD)
LEFT JOIN CLINICAL_EVENT ESDP ON (ESDP.ENCNTR_ID = E.ENCNTR_ID)
AND ESDP.EVENT_CD = :p18
AND ESDP.RESULT_STATUS_CD IN (:p19, :p20, :p21)
AND ESDP.RECORD_STATUS_CD = 188 -- ACTIVE
AND ESDP.VALID_UNTIL_DT_TM > cclsql_cnvtdatetimeutc(SYSDATE, 1, 126)
LEFT JOIN (
SELECT O.* FROM ORDERS O WHERE O.ORDER_ID = (
SELECT MAX(O2.ORDER_ID) FROM ORDERS O2 WHERE O2.ENCNTR_ID = E.ENCNTR_ID AND O2.CATALOG_CD = :p22 AND O2.ACTIVE_IND = 1
)) O ON
O.ENCNTR_ID = E.ENCNTR_ID
--        LEFT OUTER JOIN (
--            SELECT * FROM ORDER_ACTION WHERE ORDER_ID = O.ORDER_ID AND ACTION_TYPE_CD IN (:p23, :p24) AND ACTION_SEQUENCE = (
--                SELECT MAX(OA2.ACTION_SEQUENCE)
--                FROM ORDER_ACTION OA2
--                WHERE OA2.ORDER_ID = O.ORDER_ID AND OA2.ACTION_TYPE_CD IN (:p23, :p24)
--            )) OA ON
--            O.ORDER_ID = OA.ORDER_ID
WHERE E.ENCNTR_ID IN (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16)
ORDER BY PERSON_ID ASC, REG_DT_TM DESC;

Answer 1

E.ENCNTR_ID将找不到，因为此子查询不在父查询的范围内，所以您需要将E表加入到此子查询中。但是，由于在此子查询中需要ENCTR_ID特定性，因此应该考虑使用CTE(WITH子句(来预取此表。

LEFT JOIN (
SELECT O.* FROM ORDERS O WHERE O.ORDER_ID = (
SELECT MAX(O2.ORDER_ID) FROM ORDERS O2 WHERE O2.ENCNTR_ID = E.ENCNTR_ID AND 
O2.CATALOG_CD = :p22 AND O2.ACTIVE_IND = 1
)) O ON
O.ENCNTR_ID = E.ENCNTR_ID

Answer 2

我主要缺少的似乎是在子查询中使用GROUP BY。我根本不需要使用派生表。只需使用GROUP BY的子查询，并在主联接查询和子查询中重复限定即可。

SELECT
E.ENCNTR_ID
,E.REASON_FOR_VISIT
-- ,D.DIAGNOSIS_DISPLAY AS DISCHARGE_DX
-- ,D.DIAG_FTDESC AS WORKING_DX
,cclsql_cnvtdatetimeutc(E.REG_DT_TM, 2, 126) as reg_dt_tm
,cclsql_cnvtdatetimeutc(E.DISCH_DT_TM, 2, 126) as disch_dt_tm
,FACILITY.DISPLAY AS FACILITY
,P.PERSON_ID
,P.NAME_FULL_FORMATTED
,cclsql_cnvtdatetimeutc(P.BIRTH_DT_TM, 2, P.BIRTH_TZ) as birth_dt_tm
,MRN.ALIAS AS MRN
,FIN.ALIAS AS FIN
,ENC_TYPE.DISPLAY AS ENCOUNTER_TYPE
,NURSE_UNIT.DISPLAY AS NURSE_UNIT
,ROOM.DISPLAY AS ROOM
,BED.DISPLAY AS BED
-- ,ADMITDOC_NAME.NAME_FULL_FORMATTED AS ADMITDOC_NAME
,DCDOC_NAME.NAME_FULL_FORMATTED AS DCDOC_NAME
,ADMIT_SRC.DISPLAY AS ADMIT_SOURCE
,ADMIT_TYPE.DISPLAY AS ADMIT_TYPE
,ESDP.RESULT_VAL AS ESDP
,DISCH_DISPOSITION.DISPLAY AS DISCH_DISPOSITION
FROM ENCOUNTER E
-- JOIN DISCHARGE D ON (D.ENCNTR_ID = E.ENCNTR_ID)
--  AND D.ACTIVE_IND = 1
--  AND D.END_EFFECTIVE_DT_TM > SYSDATE
JOIN PERSON P ON (P.PERSON_ID = E.PERSON_ID)
JOIN CODE_VALUE ENC_TYPE ON (ENC_TYPE.CODE_VALUE = E.ENCNTR_TYPE_CLASS_CD)
JOIN ENCNTR_ALIAS FIN ON (FIN.ENCNTR_ID = E.ENCNTR_ID)
AND FIN.ENCNTR_ALIAS_TYPE_CD = 1077 -- FIN CODE
AND FIN.ACTIVE_IND = 1
AND FIN.ACTIVE_STATUS_CD = 188
JOIN ENCNTR_ALIAS MRN ON (MRN.ENCNTR_ID = E.ENCNTR_ID)
AND MRN.ENCNTR_ALIAS_TYPE_CD = 1079 -- MRN CODE
AND MRN.ACTIVE_IND = 1
AND MRN.ACTIVE_STATUS_CD = 188
JOIN CODE_VALUE FACILITY on (FACILITY.CODE_VALUE = E.LOC_FACILITY_CD)
JOIN CODE_VALUE NURSE_UNIT on (NURSE_UNIT.CODE_VALUE = E.LOC_NURSE_UNIT_CD)
JOIN CODE_VALUE ROOM on (ROOM.CODE_VALUE = E.LOC_ROOM_CD)
JOIN CODE_VALUE BED on (BED.CODE_VALUE = E.LOC_BED_CD)
JOIN CODE_VALUE DISCH_DISPOSITION on (DISCH_DISPOSITION.CODE_VALUE = E.DISCH_DISPOSITION_CD)
LEFT JOIN ORDERS O ON
O.ENCNTR_ID = E.ENCNTR_ID AND O.CATALOG_CD = :p22 AND O.ACTIVE_IND = 1 AND O.ORDER_ID IN (
SELECT MAX(O2.ORDER_ID) FROM ORDERS O2 WHERE O2.ENCNTR_ID = E.ENCNTR_ID AND O2.CATALOG_CD = :p22 AND O2.ACTIVE_IND = 1 GROUP BY O2.ENCNTR_ID
)
LEFT JOIN ORDER_ACTION OA ON
OA.ORDER_ID = O.ORDER_ID AND OA.ACTION_TYPE_CD IN (:p23, :p24) AND OA.ACTION_SEQUENCE IN (
SELECT MAX(OA2.ACTION_SEQUENCE) FROM ORDER_ACTION OA2 WHERE OA2.ORDER_ID = O.ORDER_ID AND OA2.ACTION_TYPE_CD IN (:p23, :p24) GROUP BY OA2.ORDER_ID
)
LEFT JOIN PRSNL ADMITDOC_NAME ON (ADMITDOC_NAME.PERSON_ID = OA.ORDER_PROVIDER_ID)
LEFT JOIN CLINICAL_EVENT HOSP_SUM ON (HOSP_SUM.ENCNTR_ID = E.ENCNTR_ID)
AND HOSP_SUM.EVENT_CD = :p17
AND HOSP_SUM.RESULT_STATUS_CD IN (:p19, :p20, :p21)
AND HOSP_SUM.RECORD_STATUS_CD = 188 -- ACTIVE
AND HOSP_SUM.VALID_UNTIL_DT_TM > cclsql_cnvtdatetimeutc(SYSDATE, 1, 126)
LEFT JOIN PRSNL DCDOC_NAME ON (DCDOC_NAME.PERSON_ID = HOSP_SUM.VERIFIED_PRSNL_ID)
JOIN CODE_VALUE ADMIT_SRC ON (ADMIT_SRC.CODE_VALUE = E.ADMIT_SRC_CD)
JOIN CODE_VALUE ADMIT_TYPE ON (ADMIT_TYPE.CODE_VALUE = E.ADMIT_TYPE_CD)
LEFT JOIN CLINICAL_EVENT ESDP ON (ESDP.ENCNTR_ID = E.ENCNTR_ID)
AND ESDP.EVENT_CD = :p18
AND ESDP.RESULT_STATUS_CD IN (:p19, :p20, :p21)
AND ESDP.RECORD_STATUS_CD = 188 -- ACTIVE
AND ESDP.VALID_UNTIL_DT_TM > cclsql_cnvtdatetimeutc(SYSDATE, 1, 126)
WHERE E.ENCNTR_ID IN (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16)
ORDER BY PERSON_ID ASC, REG_DT_TM DESC