OraFAQ Forum: Reports & Discoverer » how to get the figure in word

Home » Developer & Programmer » Reports & Discoverer » how to get the figure in word

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get the figure in word [message #612457]

Fri, 18 April 2014 23:23

irfankundi786@yahoo.com
Messages: 269
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?

Report message to a moderator

Re: how to get the figure in word [message #612463 is a reply to message #612457]

Sat, 19 April 2014 01:50

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: how to get the figure in word [message #612476 is a reply to message #612463]

Sat, 19 April 2014 08:58

irfankundi786@yahoo.com
Messages: 269
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

Report message to a moderator

Re: how to get the figure in word [message #612479 is a reply to message #612476]

Sat, 19 April 2014 11:34

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: how to get the figure in word [message #613984 is a reply to message #612476]

Wed, 14 May 2014 02:43

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

Report message to a moderator

Re: how to get the figure in word [message #614000 is a reply to message #613984]

Wed, 14 May 2014 05:18

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Use Trunc Function.

Select trunc(56555.50) from dual;
56555

Regards,
Irfan

Report message to a moderator

Re: how to get the figure in word [message #614003 is a reply to message #614000]

Wed, 14 May 2014 05:42

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	How to add Error Msg when format is XML Oracle Repor Builder 6i
Next Topic:	How to extend repeating frame to more than one page

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 18:09:27 CST 2025