help me out of this query

how to display salaries of emp table in words

examplee:

800 as EIGHT HUNDRED
2975 as Two Thousand Nine Hundred Seventy Five
3000 as Three Thousand
950 as nine hundred fifity

Long time ago I found this beauty in some place...

Code:

---

SQL> create or replace function num2word(num in number)
  2  return varchar2
  3  as
  4  begin
  5    return  to_char(to_date(num, 'j'),'JSPTH');
  6  END;
SQL> /

Function created.


SQL> select num2word(2975) from dual;

NUM2WORD(2975)
--------------------------------------------------------------------------------
TWO THOUSAND NINE HUNDRED SEVENTY-FIFTH

---

Enjoy it.

Code:

---


SQL> set echo on feedba on
SQL> get cr_fn
  1  create or replace function num2word(num in number)
  2    return varchar2
  3    as
  4    begin
  5    return  to_char(to_date(num, 'j'),'JSPTH');
  6*  END;
  7  /

Function created.

SQL> select num2word(987654321) from dual;
select num2word(987654321) from dual
      *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
ORA-06512: at "SYS.STANDARD", line 167
ORA-06512: at "OPS$ORACLE.NUM2WORD", line 5

---

Quote:

---

Originally Posted by tamilselvan

Code:

---

SQL> select num2word(987654321) from dual;
select num2word(987654321) from dual
      *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
ORA-06512: at "SYS.STANDARD", line 167
ORA-06512: at "OPS$ORACLE.NUM2WORD", line 5

---

---

$987,654,321 seams too much for a paycheck.
If you are doing that much please let me know how to apply for a job there.

from asktom

Code:

---

create or replace
  2  function spell_number( p_number in number )
  3  return varchar2
  4  as
  5      type myArray is table of varchar2(255);
  6      l_str    myArray := myArray( '',
  7                            ' thousand ', ' million ',
  8                            ' billion ', ' trillion ',
  9                            ' quadrillion ', ' quintillion ',
 10                            ' sextillion ', ' septillion ',
 11                            ' octillion ', ' nonillion ',
 12                            ' decillion ', ' undecillion ',
 13                            ' duodecillion ' );
 14
 15      l_num  varchar2(50) default trunc( p_number );
 16      l_return varchar2(4000);
 17  begin
 18      for i in 1 .. l_str.count
 19      loop
 20          exit when l_num is null;
 21
 22          if ( substr(l_num, length(l_num)-2, 3) <> 0 )
 23          then
 24            l_return := to_char(
 25                            to_date(
 26                              substr(l_num, length(l_num)-2, 3),
 27                                'J' ),
 28                        'Jsp' ) || l_str(i) || l_return;
 29          end if;
 30          l_num := substr( l_num, 1, length(l_num)-3 );
 31      end loop;
 32
 33      return l_return;
 34  end;
 35  /

---

Quote:

---

$987,654,321 seams too much for a paycheck.
If you are doing that much please let me know how to apply for a job there.

---

Try HOME Depot's CEO post. You will get much more than $987M.

Quote:

---

Originally Posted by tamilselvan

Try HOME Depot's CEO post. You will get much more than $987M.

---

Nope, he doesn't.

Either you have to do better research or sharpen your BA skills. LMAO

Don't worry, even if you live in Rwanda Davey's solution will work for you.