Another snippet from our work in the Forum (since we are on vacation this week) to fill your days with Oracle Intelligent Advisor goodness. The question this time relates to the failure of a concatenation when one of the text strings is unknown. This is a very common issue and the function that can fix the problem is still relatively new (in our eyes – but actually it has been around for years!)
If you are not a regular visitor to the Oracle Intelligent Advisor Forum then you are missing out on valuable experience, exchanges of opinion and great knowledge sharing.
In cases where you need to handle Unknown values in such a situation, the function DefaultWithUnknown() can help. So in this case, we will handle the situation by providing a default piece of text when the attribute has nothing and is unknown. The piece of text might just be “” or “NO COMMENT” but that is up to you.
For reference, this concept is not limited to text attributes and could provide defaults for all sorts of things in a project. A link to the relevant documentation is shown at the end of this article.
Here is an example rule that either uses what the user enters or replaces unknown with “NO ADDITIONAL INFO”.
The “NO ADDITIONAL INFO” string is the default which will be applied if the attribute the additional information is unknown or uncertain, thereby always returning a known result and helping a concatenation rule function properly. So the basic rules would look like this:
I’ve walked through a full example using the rule above and a simple interview in a short commented video :
The demonstration Zip Archive I used in the video can be found here as well in the Online Shop.
Documentation Reference :