lundi 13 juin 2016

SQL select with subquery in join condition

While I am not an advanced SQL programmer, I am trying to write SQL select, where I need put subquery into join condition in order to obtain data from the Actor table. So I have select:

SELECT JEI.account_number                                AS JEI_accountNumber,
  JE.branch_code                                         AS JE_branchCode,
  JE.journal_entry_status                                AS JEI_transStatus,
  JEI.trans_amount                                       AS JEI_transAmount,
  JEI.account_currency                                   AS JEI_accountCurrency,
  JEI.other_account_number                               AS JEI_otherAccountNumber,
  JEI.other_bank_number                                  AS JEI_otherBankNumber,
  nts_rep_cheque.getISOStringFromDate(JE.effective_date) AS JE_effectiveDate,
  JE.multiple_trans_id                                   AS JE_multipleTransID,
  A.user_logon_name                                      AS USER_ID,
  A.di_teller_id                                         AS A_DITellerID,
  nts_rep_cheque.getISOStringFromDate(JE.created_date)   AS JE_createdDate,
  JE.operation_id                                        AS JE_operationID,
  JEI.nts_di_trans_number                                AS JEI_transType
FROM nts_journal_entry JE
JOIN nts_journal_entry_item JEI
ON JEI.company_code      = JE.company_code
AND JEI.user_id          = JE.user_id
AND JEI.journal_date     = JE.journal_date
AND JEI.journal_entry_id = JE.journal_entry_id
JOIN actor A
ON A.ACCE_PROC_ID            = JE.COMPANY_CODE
AND A.actor_id               = JE.actor_id
WHERE JE.company_code        = '01'

However I want select different row from actor table using this select:

 (SELECT USER_ID
    FROM
      (SELECT USER_ID
      FROM NTS_JOURNAL_ENTRY
      WHERE MULTIPLE_TRANS_ID = JE.MULTIPLE_TRANS_ID
      ORDER BY journal_entry_id
      )
    WHERE rownum =1
    )

I put it in previous select replacing:

AND A.actor_id = JE.actor_id

to

  AND A.actor_id  = (SELECT USER_ID
    FROM
      (SELECT USER_ID
      FROM NTS_JOURNAL_ENTRY
      WHERE MULTIPLE_TRANS_ID = JE.MULTIPLE_TRANS_ID
      ORDER BY journal_entry_id
      )
    WHERE rownum =1
    )

However, it seems that Oracle has limit on nested queries, so I get an error: ORA-00904: "JE"."MULTIPLE_TRANS_ID" invalid identifier.

Is there any solution, how to fix that error or any way how to rewrite query to get same results? Thanks

Aucun commentaire:

Enregistrer un commentaire