sql - Oracle - 重写此查询以不使用子选择的方法？

Question

我正在尝试在 Oracle 11.1 中创建一个物化 View ，它显然不支持物化 View 中的嵌套选择(这是为什么，我一直无法弄清楚)..有没有办法写此查询用裁剪化 View ？谢谢!

CREATE MATERIALIZED VIEW MV_Area90DayReport
  NOLOGGING
  CACHE
  BUILD IMMEDIATE 
  REFRESH FAST ON COMMIT 
  AS 
    select
      T.TASKID,
      V.PROJECTID,
      V.VERSIONID,
      T.GOLDDATE,
      P.BUSINESSLAUNCHDATE,
      V.NAME as ProjectName,
      T.NAME as TaskName,
      T.COURSECODE,
      DT.NAME as DeliveryMethod,
      T.DELIVERABLELENGTHHOUR,
      T.DELIVERABLELENGTHMINUTE,
      V.PRIORITY,
      (SELECT MIN(STARTDATE) FROM TPM_TRAININGPLAN WHERE PROJECTID=V.PROJECTID AND TRAININGPLANTYPE='prescribed') as TrainingDeliveryDate,
      (SELECT wm_concat(WORKGROUPID) FROM TPM_PROJECTWORKGROUPS WHERE PROJECTID=V.PROJECTID GROUP BY PROJECTID) as Workgroups,
    from TPM_TASK T
    inner join TPM_PROJECTVERSION V ON (V.PROJECTID = T.PROJECTID AND V.VERSIONID = T.VERSIONID)
    inner join TPM_PROJECT P ON (P.PROJECTID = T.PROJECTID)
    inner join TPM_DOCUMENTTYPE DT ON (DT.DOCUMENTTYPEID = T.DOCUMENTTYPEID);

我得到的错误是:

>[Error] Script lines: 1-25 -------------------------
 ORA-22818: subquery expressions not allowed here
 Script line 20, statement line 20, column 115 

Answer 1

我认为这是一个限制(以前曾作为错误提出)，记录在 Oracle 站点上 - http://download.oracle.com/docs/cd/B12037_01/server.101/b10736/basicmv.htm#sthref431

要解决，您应该使用 JOINS 而不是子查询。