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