help me out of this query

[ahmsha1]

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

[PAVB]

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

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.

[tamilselvan]

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

[PAVB]

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.

[davey23uk]

from asktom

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  /

[tamilselvan]

$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.

[PAVB]

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.