We currently have a requirement on a Report Designer layout to convert a Number to a Word.
So what I mean and as an example, a number of ‘100’ to be converted to ‘One hundred’.
I have used the below SQL in a CF - which works fine.
WITH cte AS
(
select '-100' AS num
from dual
)
SELECT
num AS old_value,
decode( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL )
|| decode( sign( abs(num) ), +1, to_char( to_date( abs(TRUNC(num)),'J'),'JSP') )
||
CASE
WHEN INSTR (num, '.') > 0
THEN ' POINT ' || TO_CHAR (TO_DATE (TO_NUMBER (RPAD(SUBSTR(num, INSTR (num, '.') + 1)
,2,'0')
),'J'),'JSP')
ELSE NULL
END AS new_value
FROM cte;
But, if the number if I enter a number of ‘-6313343’ I am getting this error message.
ORA-01854: Julian date must be between 1 and 5373484
Is anybody aware of any else that I could use to get the ‘number to word’ conversation working to allow me to use it in a layout?
Any help would be great!
