Converting Request Parameter or Request Detail Parameter to Number
To enable converting request parameter or request detail parameter to number, you can use the new KNTA_USER_UTIL function in request type SQL defaulting rules:
KNTA_USER_UTIL.tonumber(string)
Example 1
PPM changes Oracle session parameter according to the regional settings and saves values for fields in CODE parameter (PARAMETER COLUMN), not in MEANING (VISIBLE_PARAMETER column). If you have users coming from countries that use non-English format numbers, for example, using comma as decimal separator, they may encounter invalid number error when they perform portlet queries that involve system function to_number
and VISIBLE_PARAMETER column values.
In this case, as administrators, you can write SQL queries similar to the follows for them using the KNTA_USER_UTIL.tonumber
function, which always fetches CODE parameter values:
SELECT sum(KNTA_USER_UTIL.tonumber(NVL(krv.parameter25,'0'))) FROM kcrt_requests_v krv
WHERE 1=1
Example 2
If you want to make value of Field3 equal to the result of Field1 multiplying Field2, use the following SQL rule:
Select KNTA_USER_UTIL.tonumber('[REQD.P.Field1] ')*KNTA_USER_UTIL.tonumber('[REQD.P.Field2] '),KNTA_USER_UTIL.tonumber('[REQD.P.Field1] ')*KNTA_USER_UTIL.tonumber('[REQD.P.Field2] ') from dual