Question

5
Replies
971
Views
SALAZONE Member since 2006 46 posts
PEGA
Posted: 3 years ago
Last activity: 3 years 2 months ago
Closed
Solved

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]

Data Integration Reporting
Moderation Team has archived post
Share this page LinkedIn