CODEWITHSUNDEEP
sqloracle-database
baboufight
baboufight

Reputation: 133

Oracle SQL - Get same result without using a WITH clause

I've a query which uses a WITH clause, to be able to calculate some flags (IS_EMPLOYE, IS_AFFILIE) in the SQL result. But I need to find an equivalent of this query without using WITH clause, because the soft who will execute the SQL don't allow the WITH clause. (I've already thought about using views instead the WITH clause, but I don't have permission to create or change anything in the database).

This is the query :

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;

Upvotes: 2

Views: 155

Answers (1)

user330315
user330315

Reputation:

As you are referencing each CTE only once, you can rewrite them as derived tables:

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;

Upvotes: 5

Related Questions