oracle12c - 无法在以下查询中创建物化视图

Question

希望你们一切都好。我能够执行下面的代码并在 oracle 中获得正确的输出。但是在尝试创建视图时，它会给出错误 - 列可能未加入子查询。该视图是在同一查询的 sql server 中创建的，但在 oracle 中它给出了一个错误。请帮助。

select c.Contractid
FROM    FCT_EIT_CONTRACT c LEFT OUTER JOIN
DIM_EIT_PROCESS_INSTANCE psi   ON psi.SURR_ID = c.PROCESS_INSTANCE_SKEY 
    LEFT OUTER JOIN                 
    DIM_EIT_ACTION wa               ON psi.ACTION_SKEY wa.SURR_ID 
    LEFT OUTER JOIN         
    DIM_EIT_ACTION_HISTORY  ah      ON wa.SURR_ID = ah.ACTION_SKEY 

    AND psi.SURR_ID = ah.PROCESS_INSTANCE_SKEY 
    AND ah.ACTION_HISTORY_ID =(SELECT  MAX(ACTION_HISTORY_ID) AS Expr1
   FROM  DIM_EIT_ACTION_HISTORY  a
   WHERE (PROCESS_INSTANCE_SKEY = c.PROCESS_INSTANCE_SKEY) 
   AND 
  (psi.ACTION_SKEY = ACTION_SKEY)))  
  LEFT OUTER JOIN
 DIM_EIT_STEP ws ON ws.SURR_ID = wa.NEXT_STEP_SKEY
 WHERE (psi.CurrentStepId NOT IN (100, 130, 131, 132, 900, 902, 910)) 

标签： oracle12c

Answer 1

如果您将查询的违规部分移到WHERE子句中会有所帮助吗，例如

SELECT c.contractid
FROM fct_eit_contract c
LEFT OUTER JOIN dim_eit_process_instance psi ON psi.surr_id = c.process_instance_skey
LEFT OUTER JOIN dim_eit_action wa            ON psi.action_skey wa.surr_id
LEFT OUTER JOIN dim_eit_action_history ah    ON wa.surr_id = ah.action_skey
                                            AND psi.surr_id = ah.process_instance_skey
LEFT OUTER JOIN dim_eit_step ws              ON ws.surr_id = wa.next_step_skey
WHERE psi.currentstepid NOT IN (100, 130, 131, 132, 900, 902, 910)
  -- subquery moved down here
  AND  ah.action_history_id = (SELECT MAX(action_history_id) AS expr1
                               FROM dim_eit_action_history a
                               WHERE process_instance_skey = c.process_instance_skey
                                 AND psi.action_skey = action_skey
                              );