Search not working for LOV in BI Publisher Report

Summary

Search not working for LOV in BI Publisher Report

Content

Hi Team,

I have created LOV through SQL in BI Publisher Report. If i am using value without transforming , search is working fine. As soon as i start using to_date or to_char function in the SQL, search stops working. Below are the SQL for the LOV :

 

LOV Query  : User is able to search Manually 

 

SELECT GP.period_name as pname

 FROM gl_periods gp

 

 

LOV Query : 'User is not able to Search , even though value is available in the list. 

SELECT TO_DATE(gp.period_name,'Mon-yy', 'NLS_DATE_LANGUAGE=ENGLISH') as pname