sql - Oracle SQL-在不使用WITH子句的情况下获得相同的结果

我有一个查询，该查询使用WITH子句，以便能够在SQL结果中计算一些标志(IS_EMPLOYE，IS_AFFILIE)。但是我需要在不使用WITH子句的情况下找到该查询的等效项，因为执行SQL的软件不允许使用WITH子句。
(我已经考虑过使用 View 代替WITH子句，但是我无权在数据库中创建或更改任何内容)。

这是查询:

WITH
    pty_id AS (
    SELECT pty.PARTY_ID
    FROM PTY_ROL_REL prr
    JOIN PARTY pty ON prr.PRREL_PTY_ID = PTY.PARTY_ID
    WHERE prr.PTY_ROL_REL_ID = <an ID provided by a variable>
    ),
    affi AS (
    SELECT prr.PRREL_PTY_ID,
    Count(*) ISAFFILIE
    FROM PTY_ROL_REL prr
    JOIN PTY_ROL_STA_REL prsr
    ON prr.PTY_ROL_REL_ID = prsr.PRSRE_PTY_ROL_REL_ID AND prsr.PRSRE_PTY_STA_CD = '01'
    WHERE prr.PRREL_PTY_ROL_CD IN ('001','002')
    GROUP BY prr.PRREL_PTY_ID
    ),
    empl AS (
    SELECT PRREL_PTY_ID,
    Count(*) ISEMPLOYE
    FROM PTY_ROL_REL
    GROUP BY PRREL_PTY_ID
    ),
SELECT
PARTY.PARTY_ID PKEY_SRC_OBJECT,
ROWIDTOCHAR(PARTY.ROWID) SRC_ROWID,
PARTY.PARTY_ODS_MODF_DAT LAST_UPDATE_DATE,
decode(nvl(affi.ISAFFILIE, 0), 0, 0, 1) AS IS_AFFILIE,
decode(nvl(empl.ISEMPLOYE, 0), 0, 0, 1) AS IS_EMPLOYE
FROM PARTY
JOIN pty_id ON PARTY.PARTY_ID = pty_id.PARTY_ID
JOIN affi ON  pty_id.PARTY_ID = affi.PRREL_PTY_ID
JOIN empl ON pty_id.PARTY_ID = empl.PRREL_PTY_ID;

最佳答案

由于仅引用每个CTE一次，因此可以将它们重写为派生表:

SELECT party.party_id pkey_src_object,
       rowidtochar(party.rowid) src_rowid,
       party.party_ods_modf_dat last_update_date,
       decode(nvl(affi.isaffilie, 0), 0, 0, 1) as is_affilie,
       decode(nvl(empl.isemploye, 0), 0, 0, 1) as is_employe
FROM PARTY
  JOIN (
      SELECT pty.PARTY_ID
      FROM pty_rol_rel prr
        JOIN party pty ON prr.PRREL_PTY_ID = PTY.PARTY_ID
      WHERE prr.PTY_ROL_REL_ID = <an ID provided by a variable>
  ) pty_id ON party.party_id = pty_id.party_id
  JOIN (
      SELECT prr.PRREL_PTY_ID,
             Count(*) ISAFFILIE
      FROM pty_rol_rel prr
         JOIN pty_rol_sta_rel prsr ON prr.PTY_ROL_REL_ID = prsr.PRSRE_PTY_ROL_REL_ID
                                  AND prsr.PRSRE_PTY_STA_CD = '01'
      WHERE prr.PRREL_PTY_ROL_CD IN ('001','002')
      GROUP BY prr.PRREL_PTY_ID

  ) affi ON  pty_id.PARTY_ID = affi.PRREL_PTY_ID
  JOIN (
      SELECT PRREL_PTY_ID,
             Count(*) ISEMPLOYE
      FROM pty_rol_rel
      GROUP BY PRREL_PTY_ID
  ) empl ON pty_id.PARTY_ID = empl.PRREL_PTY_ID;