Muhammad Zeeshan Karamat Muhammad Zeeshan Karamat - 3 months ago 9
SQL Question

Oracle is executing NVL second argument query when the first argument is not null

Ideally, query given in the second argument should only be executed for the NVL to display alternate text when the first argument is null. But in the below query the NVL is executing the query when the first argument is not null and I am getting string concatenation overflow error because of list_agg used in the second argument query of the NVL.

SELECT NVL (tb1.related_account,
(SELECT DISTINCT LISTAGG (A.ac_no, ';') WITHIN GROUP (ORDER BY A.ac_no)
FROM ACVW_ALL_AC_ENTRIES A
WHERE tb1.trn_ref_no IS NOT NULL AND A.trn_ref_no = tb1.trn_ref_no AND A.trn_code = tb1.trn_code AND A.event = tb1.event AND A.ac_no <> TB1.AC_NO AND A.ac_branch = :param_branch)) FROM ACVW_ALL_AC_ENTRIES tb1
INNER JOIN sttb_account tb2 ON tb1.ac_no = tb2.AC_GL_NO
INNER JOIN gltm_glmaster tb3 ON tb1.AC_NO = tb3.gl_code
INNER JOIN ACTB_ACCBAL_HISTORY tb6 ON tb6.Account = TB1.AC_NO AND TB6.BKG_DATE = TB1.TRN_DT AND TB1.AC_BRANCH = TB6.BRANCH_CODE
LEFT OUTER JOIN DETB_RTL_TELLER tb4 ON tb1.TRN_REF_NO = tb4.TRN_REF_NO
-- AND TB1.TRN_DT=TB4.TRN_DT
INNER JOIN STTM_BRANCH tb5 ON tb1.AC_BRANCH = tb5.BRANCH_CODE
INNER JOIN sttm_trn_code ON sttm_trn_code.trn_code = tb1.TRN_CODE
LEFT OUTER JOIN swtb_txn_log ON SWTB_TXN_LOG.TRN_REF_NO = TB1.TRN_REF_NO WHERE CUST_GL = 'G' AND TB1.TRN_DT = :Dated AND AC_BRANCH IN (:param_branch) AND tb1.related_account IS NOT NULL


How will I avoid running the query given in the second argument of NVL when the first argument of the NVL is null.

Answer

You can use COALESCE instead:

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

There is also the advantage that it's standard SQL, rather than the proprietary NVL.

Comments