Using an NVL in a report definition
I have a Function Alias called "NVL" which uses the NVL(string1, string2) function from Oracle. The source of the function alias contains the following code: NVL({1},{2})
In my report definition I have the following filter criteria: .
PublishedCourse.CourseName contains NVL[ParamPage.ContentSearchText] [nomatch]
When I use "work" as a search term, tracer shows the actual SQL to be:
UPPER("PC0".COURSENAME_3) = UPPER(NVL(work ,nomatch ))
This does not produce any results, nor should it because the SQL statement is checking for EQUALITY when I am using CONTAINS. Why does CONTAINS translate to = when using this function alias? What do I do about that?
When I remove the function alias and simply use:
PublishedCourse.CourseName contains ParamPage.ContentSearchText
Tracer shows:
UPPER("PC0".COURSENAME_3) LIKE %WORK%
I get results. The problem with this is that when no search term is entered, all results are returned which is unacceptable; I should see no results since no term was entered. That's why I want to use NVL[ParamPage.ContentSearchText] [nomatch]
Accepted Solution
Pegasystems
IN
I removed the NVL function from the Value field and put it on the Column source field, so in the RD I have:
NVL [PublishedCourse.CourseName] [nomatch] contains ParamPage.ContentSearchTextI ran it but I'm still getting all results as if the search term was null. I traced it and discovered that there is no SQL entry for this filter criteria, only the SQL from the previous and successive filter criteria. Did I misunderstand the resolution from the referenced article?
If the "Value" field results in null or empty, that filter condition is ignored. Based on your description above, it looks like "ParamPage.ContentSearchText" is empty or the property doesn't exist on that page. You can force it to convert it to null by going to the settings icon next to the filter in the report definition rule form and check "Use NULL if empty".
Pegasystems
IN
Please refer to this support article - https://community.pega.com/support/support-articles/function-not-working-properly-report-definition-filter
Pegasystems Inc.
US
Thanks for the reply. The reference article says, "Use SQL function for the column source field in filter criteria instead of value field and such that user can generate the contains("like") logic in SQL".
I removed the NVL function from the Value field and put it on the Column source field, so in the RD I have:
NVL [PublishedCourse.CourseName] [nomatch] contains ParamPage.ContentSearchText
I ran it but I'm still getting all results as if the search term was null. I traced it and discovered that there is no SQL entry for this filter criteria, only the SQL from the previous and successive filter criteria. Did I misunderstand the resolution from the referenced article?
Accepted Solution
Pegasystems
IN
I removed the NVL function from the Value field and put it on the Column source field, so in the RD I have:
NVL [PublishedCourse.CourseName] [nomatch] contains ParamPage.ContentSearchTextI ran it but I'm still getting all results as if the search term was null. I traced it and discovered that there is no SQL entry for this filter criteria, only the SQL from the previous and successive filter criteria. Did I misunderstand the resolution from the referenced article?
If the "Value" field results in null or empty, that filter condition is ignored. Based on your description above, it looks like "ParamPage.ContentSearchText" is empty or the property doesn't exist on that page. You can force it to convert it to null by going to the settings icon next to the filter in the report definition rule form and check "Use NULL if empty".
Pegasystems Inc.
US
I'd like to correct myself in my original post. I wrote "I have a Function Alias called "NVL" which uses the NVL(string1, string2) function from Oracle" but Oracle does not provide Function Aliases - this was created by another Pega developer.