Home » Developer & Programmer » Reports & Discoverer » how to get the figure in word
how to get the figure in word [message #612457] Fri, 18 April 2014 23:23 Go to next message
irfankundi786@yahoo.com
Messages: 219
Registered: February 2009
Location: pakistan
Senior Member

I am using oracle report 10 g i want to convert the figure like 45600 into the word like fourty five thousand and six hundered only..how i can do this?
Re: how to get the figure in word [message #612463 is a reply to message #612457] Sat, 19 April 2014 01:50 Go to previous messageGo to next message
Littlefoot
Messages: 19635
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> select to_char(to_date(45600, 'j'), 'jsp') from dual;

TO_CHAR(TO_DATE(45600,'J'),'JSP
-------------------------------
forty-five thousand six hundred

SQL>
Re: how to get the figure in word [message #612476 is a reply to message #612463] Sat, 19 April 2014 08:58 Go to previous messageGo to next message
irfankundi786@yahoo.com
Messages: 219
Registered: February 2009
Location: pakistan
Senior Member

but i hear about this that it has some limitation ..Can u tell in what phase this is not working
Re: how to get the figure in word [message #612479 is a reply to message #612476] Sat, 19 April 2014 11:34 Go to previous messageGo to next message
Littlefoot
Messages: 19635
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is about max Julian date Oracle can display (which is 31.12.9999):
SQL> select to_date(5373484, 'j') from dual;

TO_DATE(53
----------
31.12.9999
If you try with that value + 1, you'll get
SQL> select to_date(5373485, 'j') from dual;
select to_date(5373485, 'j') from dual
               *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
so - obviously - this principle allows values up to 5.373.484.

For values up to 999.999.999 you can use
SQL> select to_char(to_timestamp(lpad(45600, 9, '0'), 'FF9' ), 'FFSP') from dual;

TO_CHAR(TO_TIMESTAMP(LPAD(45600,9,'0'),'FF9'),'FFSP')
------------------------------------------------------------------------------
FORTY-FIVE THOUSAND SIX HUNDRED


For more info & useful code, enjoy reading Ask Tom.
Re: how to get the figure in word [message #613984 is a reply to message #612476] Wed, 14 May 2014 02:43 Go to previous messageGo to next message
skumar16jan
Messages: 6
Registered: May 2014
Location: Malaysia
Junior Member

Hi,

Main limitation is decimal value can not be pass.

e.g. Rs. 56555.50/- can not display

Only Rs. 56555/- can display

Regards,
Satinder kumar
Re: how to get the figure in word [message #614000 is a reply to message #613984] Wed, 14 May 2014 05:18 Go to previous messageGo to next message
ranamirfan
Messages: 529
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Use Trunc Function.
Select trunc(56555.50) from dual;
56555


Regards,
Irfan
Re: how to get the figure in word [message #614003 is a reply to message #614000] Wed, 14 May 2014 05:42 Go to previous message
Littlefoot
Messages: 19635
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
skumar16jan

Main limitation is decimal value can not be pass.


ranamirfan

Use Trunc Function.


Not really; with TRUNC, you lose information. If you want to spell 56555.50, then it means exactly this: 56555.50, not 56555.

With decimal numbers, you have to split input value into two parts: before and after the decimal point, and spell both parts separately. For example:
SQL> WITH test AS (SELECT 56555.5730 col FROM DUAL),
  2       t_split
  3          AS (SELECT TRUNC (col) c_before, col - TRUNC (col) c_after FROM test),
  4     t_results as(
  5  SELECT TO_CHAR (TO_DATE (c_before, 'j'), 'jsp') r_before,
  6         TO_CHAR (
  7            TO_DATE (
  8               TO_NUMBER (
  9                  SUBSTR (TO_CHAR (c_after),
 10                          INSTR (TO_CHAR (c_after), ',') + 1,
 11                          LENGTH (TO_CHAR (c_after)))),
 12               'j'),
 13            'jsp')
 14            r_after
 15    FROM t_Split)
 16    select r_before ||' point ' || r_after
 17    from t_results;

R_BEFORE||'POINT'||R_AFTER
---------------------------------------------------------------------------
fifty-six thousand five hundred fifty-five point five hundred seventy-three

SQL>

Probably you can write it prettier, but - that's just a general idea.
Previous Topic: REP-1247: Uncompiled program units exist. REP-1247: The report contains uncompiled PL/SQL.
Next Topic: How to extend repeating frame to more than one page
Goto Forum:
  


Current Time: Mon Sep 22 17:49:17 CDT 2014

Total time taken to generate the page: 0.10262 seconds