We have noticed that in few interfaces, the NVL function is not working as expected when “_simple_view_merging” is set to be true in the Oracle source database.
This problem is encountered if the column corresponding to the first function displays the correct results, but the columns corresponding to other functions return nothing. You can see this when a nested query that has several functions in an outer query block, points to the same function in the inner query block, and “_simple_view_merging” is set to be true.
You can fix this in the ODI interface by passing the parameter through the ETL_HINT_NESTED variable and setting “_simple_view_merging” to False in the source database.
Here is the fix:
Add the variable ETL_HINT_NESTED before the interface and pass the string “/*+ opt_param(‘_simple_view_merging’,’false’) */ “ into the package of the interface experiencing the issue.