% Complete field on PM Views throws 500 Error when used in Power Filter

Products

• Clarity PPM On Premise

Issue/Introduction

When used the field in PM Views as power filter, we are getting 500 Error message on Save and Return. We built a portlet based on NSQL where we are pulling % Complete field and converting to String from inv_projects table but the field is of % data type in Project Object.

Environment

Release : 15.9

Component :

Cause

Using input: 
{is_active=1, percentage_complete=100%, @[email protected]= ( @[email protected](pp.percent_complete*100.00 || '%') != 100 ) , nsql.execFlag=1}
Using dynamic SQL: 
tag: @[email protected] SQL: ( @[email protected](pp.percent_complete*100.00 || '%') != 100 ) 
tag: @[email protected] SQL:1 = 1
tag: @[email protected] SQL: order by id asc

 at com.niku.union.persistence.PersistenceController.createException(PersistenceController.java:2224)
 at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:2404)
 at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:3091)
 at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:888)
 at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:786)
 at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:588)
 at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:311)
 at com.niku.union.persistence.nsql.NSQLQuery._execute(NSQLQuery.java:683)
 ... 77 more
Caused by: java.sql.SQLDataException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01722: invalid number

Resolution

Do not convert % Compete field to String because when NSQL portlet is executed and it is attempting to filter it's expecting a number so error is thrown.