-
There are some limitations in using this trick that you must be avare of. Take a look at http://www.dbasupport.com/forums/sho...threadid=11319
Also (although probably not so inportant now in the era of 8i and 9i), there was a bug in 7.2.x I think where some integers were not spelled correctly.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Moved from administration forum to the development forum. As this question would be more appropriate under this forum
Sam
Thanx
Sam
Life is a journey, not a destination!
-
You cannot use JSP for numbers
greater than 5373484
SQL> select to_char( to_date(5373485 ,'J'),'Jsp') from dual
2 /
ERROR:
ORA-01854: julian date must be between 1 and 5373484
Ofcourse you can overcome this using a small pl/sql function using the 'jsp' format.
-
Hi csvenkata
What if we have 111.56 ?
-
Originally posted by aph
Hi csvenkata
What if we have 111.56 ?
Julian dates in Oracle are allways represented by integers (they represent only the date portion without the time), so you must convert your decimal numbers into two integers (before and after the decimal point) and process them separately. Note that the JSP trick does not work with 0. If the number can be negative, you must use ABS() function and process the sign separately, like:
Code:
SQL> set verify off
SQL> undefine test_number
SQL> SELECT DECODE(SIGN(&&test_number), -1, 'MINUS ') ||
2 DECODE(TRUNC(&&test_number), 0, 'ZERO',
3 TO_CHAR(TO_DATE(ABS(TRUNC(&&test_number)) ,'J'),'JSP')) ||
4 ' DOLLARS, ' ||
5 DECODE(MOD(ABS(&&test_number), 1), 0, 'ZERO',
6 TO_CHAR( TO_DATE(MOD(ABS(&&test_number), 1)*100 ,'J'),'JSP')) ||
7 ' CENTS' spelled
8 FROM dual;
Enter value for test_number: 0
SPELLED
--------------------------------------------------------------------------------
ZERO DOLLARS, ZERO CENTS
SQL> undefine test_number
SQL> /
Enter value for test_number: 111.56
SPELLED
-------------------------------------------
ONE HUNDRED ELEVEN DOLLARS, FIFTY-SIX CENTS
SQL> undefine test_number
SQL> /
Enter value for test_number: 9999
SPELLED
--------------------------------------------------------------------------------
NINE THOUSAND NINE HUNDRED NINETY-NINE DOLLARS, ZERO CENTS
SQL> undefine test_number
SQL> /
Enter value for test_number: -111.56
SPELLED
-------------------------------------------------
MINUS ONE HUNDRED ELEVEN DOLLARS, FIFTY-SIX CENTS
SQL> undefine test_number
SQL> /
Enter value for test_number: 15
SPELLED
--------------------------------------------------------------------------------
FIFTEEN DOLLARS, ZERO CENTS
SQL> undefine test_number
SQL> /
Enter value for test_number: 0.99
SPELLED
--------------------------------------------------------------------------------
ZERO DOLLARS, NINETY-NINE CENTS
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Can I share my Code
select to_char(to_Date(substr(&&mynum,1,instr(&&mynum,'.')-1),'j'),'jsp') || ' POINT ' ||
to_char(to_Date(substr(&&mynum,instr(&&mynum,'.')+1,length(&&mynum)),'j'),'jsp')
from dual
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|