Using NVL2 Expression in PS Query PeopleSoft

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.

 NVL2(:1,:1,'ALL')  

Then, after lots of trial and error, the following piece of code worked wonders:

 NVL2(LTRIM(':1',' '),':1','ALL')  

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!! 🙂

Advertisements

About Manikandan Surendren

A PeopleSoft Techno-functional Consultant. An engineering graduate in Computer Science. Technology Enthusiast. Movie freak. Gadget geek.
This entry was posted in Code, Oracle, PeopleSoft. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s