Recently, I faced an issue wherein I had to create a PS Query which would return the text ALL if the bind variable was null and return the actual bind value, if non-null. Came up with the following expression, but it didn’t work.
Then, after lots of trial and error, the following piece of code worked wonders:
When it comes to a PS Query, it is essential that you handle null properly. Failure to do that will result in incorrect results. NVL2 function in Oracle returns the actual string if the string being checked is not null, and the string ALL if the string being checked is null.
You can still use CASE/DECODE to do this if the number of values/conditions that you need to check is more. For instance, all I had to check is if a particular value is null or not and accordingly write the statement.
Hope this helps! 🙂
Have fun!! 🙂