Oracle DB SQL SELECT FROM a, b INNER JOIN c : is that possible?-CodePudding

I had a SQL

SELECT dtl.ACCT_YM              AS ACCT_YM,
       src.SRC_NAME             AS SRC_NAME,
       ptnr.PTNR_NAME           AS PTNR_NAME,
       info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM MPTNR_DTL dtl,
     (SELECT SRC_ID ,SRC_NAME FROM SOURCE_VIEW WHERE DELETED = '0') src,
     (SELECT PTNR_ID ,PTNR_NAME FROM PARTNER_MV WHERE DELETED = '0') ptnr
INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
WHERE dtl.ACCT_YM = '202204'
  AND dtl.DELETED = '0'
  AND ROWNUM <= 10
ORDER BY dtl.SRC_ID;

I tried it on the SQLplus but it was a wrong SQL. The error was: dtl.ACCT_YM can not be recognized.

The basic sentence is

SELET a,b,c FROM table_1, (sub select) table_2, (sub select) table_3 INNER JOIN table_3 WHERE XXXX

How can I fix it? Thank you!

CodePudding user response：

The way I see it, it should be a join of 3 different tables:

  SELECT dtl.acct_ym, src.src_name, ptnr.ptrn_name
    FROM mptnr_dtl dtl
         JOIN source_view src ON src.src_id = dtl.src_id
         JOIN partner_mv ptnr ON ptnr.some_id = dtl.some_id      --> which ID?
         JOIN ptnr_info info ON info.acct_ym = dtl.acct_ym
   WHERE     src.deleted = '0'
         AND ptnr.deleted = '0'
         AND info.deleted = '0'
         AND dtl.deleted = '0'
         AND dtl.acct_ym = '202204'
         AND ROWNUM <= 10
ORDER BY dtl.src_id

I don't know join conditions so I guessed column names (as you didn't post table descriptions nor relations among them).

CodePudding user response：

If you really wanted a mix of legacy comma and ANSI joins (please don't) then you need to have the ANSI joins before the comma joins:

SELECT dtl.ACCT_YM              AS ACCT_YM,
       src.SRC_NAME             AS SRC_NAME,
       ptnr.PTNR_NAME           AS PTNR_NAME,
       info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM   MPTNR_DTL dtl
       INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM),
       (SELECT SRC_ID ,SRC_NAME FROM SOURCE_VIEW WHERE DELETED = '0') src,
       (SELECT PTNR_ID ,PTNR_NAME FROM PARTNER_MV WHERE DELETED = '0') ptnr
WHERE  dtl.ACCT_YM = '202204'
  AND  dtl.DELETED = '0'
  AND  ROWNUM <= 10
ORDER BY dtl.SRC_ID;

However, that is bad practice and it would be better to stick to a single join syntax throughout.

You have an additional issue that the ROWNUM is generated as the rows are read and the WHERE filter is applied before the ORDER BY clause so you will get the first 10 random rows and then order them.

From Oracle 12, the legacy comma joins and the row limiting filter can be translated to:

SELECT dtl.ACCT_YM              AS ACCT_YM,
       src.SRC_NAME             AS SRC_NAME,
       ptnr.PTNR_NAME           AS PTNR_NAME,
       info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM   MPTNR_DTL dtl
       CROSS JOIN (SELECT SRC_ID ,SRC_NAME FROM SOURCE_VIEW WHERE DELETED = '0') src
       CROSS JOIN (SELECT PTNR_ID ,PTNR_NAME FROM PARTNER_MV WHERE DELETED = '0') ptnr
       INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
WHERE  dtl.ACCT_YM = '202204'
AND    dtl.DELETED = '0'
ORDER BY dtl.SRC_ID
FETCH FIRST 10 ROWS ONLY;

Or, more simply:

SELECT dtl.ACCT_YM              AS ACCT_YM,
       src.SRC_NAME             AS SRC_NAME,
       ptnr.PTNR_NAME           AS PTNR_NAME,
       info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM   MPTNR_DTL dtl
       CROSS JOIN SOURCE_VIEW src
       CROSS JOIN PARTNER_MV ptnr
       INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
WHERE  dtl.ACCT_YM = '202204'
AND    dtl.DELETED = '0'
AND    src.DELETED = '0'
AND    ptnr.DELETED = '0'
ORDER BY dtl.SRC_ID
FETCH FIRST 10 ROWS ONLY;

In Oracle 11 and earlier, you would want to ORDER first and then filter by ROWNUM (instead of filtering on the first 10 random rows and then ordering those random rows):

SELECT *
FROM   (
  SELECT dtl.ACCT_YM              AS ACCT_YM,
         src.SRC_NAME             AS SRC_NAME,
         ptnr.PTNR_NAME           AS PTNR_NAME,
         info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
  FROM   MPTNR_DTL dtl
         CROSS JOIN (SELECT SRC_ID ,SRC_NAME FROM SOURCE_VIEW WHERE DELETED = '0') src
         CROSS JOIN (SELECT PTNR_ID ,PTNR_NAME FROM PARTNER_MV WHERE DELETED = '0') ptnr
         INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
  WHERE  dtl.ACCT_YM = '202204'
  AND    dtl.DELETED = '0'
  ORDER BY dtl.SRC_ID
)
WHERE  ROWNUM <= 10;

or:

SELECT *
FROM   (
  SELECT dtl.ACCT_YM              AS ACCT_YM,
         src.SRC_NAME             AS SRC_NAME,
         ptnr.PTNR_NAME           AS PTNR_NAME,
         info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
  FROM   MPTNR_DTL dtl
         CROSS JOIN SOURCE_VIEW src
         CROSS JOIN PARTNER_MV ptnr
         INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
  WHERE  dtl.ACCT_YM = '202204'
  AND    dtl.DELETED = '0'
  AND    src.DELETED = '0'
  AND    ptnr.DELETED = '0'
  ORDER BY dtl.SRC_ID
)
WHERE  ROWNUM <= 10;

If you want, you can change the CROSS JOINs to INNER JOIN but then you would need to specify a join condition (which is something your existing query does not have).