This discussion is archived

# better query

Currently Being Moderated
i want convert a digit to words
i have written the following code
can i simplify this code
``````create or replace FUNCTION fun_ntow
(
Digit IN NUMBER
)
RETURN VARCHAR2
IS

TYPE vmWord IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
mWord vmWord;
digword Varchar2(200);
decword Varchar2(200);
mwords  Varchar2(200);
mxctr Number;
mnum Number;
mlen Number;
mint number;
mmod number;

BEGIN

mword(1) := 'One';
mword(2) := 'Two';
mword(3) := 'Three';
mword(4) := 'Four';
mword(5) := 'Five';
mword(6) := 'Six';
mword(7) := 'Seven';
mword(8) := 'Eight';
mword(9) := 'Nine';
mword(10):= 'Ten';
mword(11) := 'Eleven';
mword(12) := 'Twelve';
mword(13) := 'Thirteen';
mword(14) := 'Fourteen';
mword(15) := 'Fifteen';
mword(16) := 'Sixteen';
mword(17) := 'Seventeen';
mword(18) := 'Eighteen';
mword(19) := 'Nineteen';
mword(20) := 'Twenty';
mword(21) := 'Thirty';
mword(22) := 'Fourty';
mword(23) := 'Fifty';
mword(24) := 'Sixty';
mword(25) := 'Seventy';
mword(26) := 'Eighty';
mword(27) := 'Ninety';
mword(28) := 'Crore';
mword(29) := 'Lakh';
mword(30) := 'Thousand';
mword(31) := 'Hundred';
mword(32) := '';
mword(33) := '';
mword(34) := '';
mwords := '';
mxctr := 28;

--digword :=  TO_CHAR(digit,'00000000.00') ;

digword :=  TO_CHAR(digit,'00000000.00') ;
decword := To_Char(digit, '.00');
mlen := 1;

WHILE mlen <= 12
LOOP

SELECT TO_NUMBER(SUBSTR(digword,mlen,decode(mlen,7,1,2)))
INTO   mnum
FROM   dual;

IF mnum > 0
THEN

IF mlen = 11
THEN
mwords := mwords || 'Point ';
END IF; --mlen = 11

IF mnum <= 20 AND mnum > 0
THEN
mwords := mwords || ' ' || mword(mnum);
ELSE
SELECT TRUNC(mnum / 10)
INTO   mint
FROM   DUAL;

SELECT MOD(mnum, 10)
INTO   mmod
FROM   DUAL;

mwords := mwords || mword(20 + mint - 2);

IF  mmod <> 0
THEN
mwords := mwords || ' ' || mword(mmod);
END IF;

END IF; --mnum <= 20 And mnum > 0

IF mlen = 11
THEN
mwords := mwords || ' ';
END IF;

mwords := mwords || ' ' || mword(mxctr) || ' ' ;

END IF; --mnum > 0

mxctr := mxctr + 1;

IF mlen = 8
THEN
mlen := mlen + 3;
ELSIF mlen <> 7
THEN
mlen := mlen + 2;
ELSE
mlen := mlen + 1;
END IF;

END LOOP; --WHILE mlen <= 12

RETURN LOWER(mwords);
END fun_ntow;``````
• ###### 1. Re: better query
Currently Being Moderated
How large are the numbers you want to convert.

There's a way it can be done simply using date functionality....
``````SQL> select to_char(to_date(12345,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(12345,'J'),'JSP')
----------------------------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE``````
though this is limited to the range of values you can use.
``````SQL> select to_char(to_date(1234567,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(1234567,'J'),'JSP')
---------------------------------------------------------------------
ONE MILLION TWO HUNDRED THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN

SQL> select to_char(to_date(12345678,'J'),'JSP') from dual;
select to_char(to_date(12345678,'J'),'JSP') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string``````
• ###### 2. Re: better query
Currently Being Moderated
max is upto 99 crores
this is in Indian rupees
• ###### 3. Re: better query
Currently Being Moderated
What is a crore?

This is an international forum and we use international terms here, so please provide the answer such as "9 million" or suchlike.
• ###### 4. Re: better query
Currently Being Moderated
around 90 million
• ###### 5. Re: better query
Currently Being Moderated
• ###### 6. Re: better query
Currently Being Moderated
``````select to_char(to_date(12345,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(12345,'J'),'JSP')
----------------------------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE``````
how does this query work
i am really not able to understand

Edited by: user in on Jan 11, 2013 4:40 PM
• ###### 7. Re: better query
Currently Being Moderated
user in wrote:
``````select to_char(to_date(12345,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(12345,'J'),'JSP')
----------------------------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE``````
how does this query work
i am really not able to understand
It takes the number and treats it as a Julian date (rather than gregorian calendar. Julian dates are a number of days since 0 January 4712, so 1 is the 1st Jan 4712) by turning it into a date with the TO_DATE function and the "J" format.
``````SQL> select to_date(1,'J') from dual;

TO_DATE(1,'J')
--------------------
01-JAN-4712 00:00:00

SQL> select to_date(2,'J') from dual;

TO_DATE(2,'J')
--------------------
02-JAN-4712 00:00:00

SQL> select to_date(12345,'J') from dual;

TO_DATE(12345,'J')
--------------------
19-OCT-4679 00:00:00``````
We then take the date and convert it back to a string using the TO_CHAR. If we did this with the same "J" format we'd get the same number of days since 0 Jan 4712 back out...
``````SQL> select to_char(to_date(12345,'J'),'J') from dual;

TO_CHAR
-------
0012345``````
But TO_CHAR also includes another format called "JSP" which is the "Julian Spelling" format, i.e. the numbers are spelt out in words.
``````SQL> select to_char(to_date(12345,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(12345,'J'),'JSP')
----------------------------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE``````
However, because it's using DATE functionality, the number being used is limited by what date's Oracle will deal with and therefore if you specify an number that is <1 or too big for the Julian dates you'll get...
``ORA-01854: julian date must be between 1 and 5373484``
So, that's the limit of numbers you can use.
``````SQL> select to_char(to_date(5373484,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR``````
If you need to deal with numbers larger than that then you have to manually implement it as described already in other threads such as Tom's.
• ###### 8. Re: better query
Currently Being Moderated

It's easy to get confused too. Don't think that the year 4712 in the Julian calander is the equivalent of year 4712 in the current gregorian calendar. They're completely different years.
``````SQL> ed
Wrote file afiedt.buf

1  select to_date(1,'J') as julian_dt
2        ,dump(to_date(1,'J')) as julian_dump
3        ,to_date('01-JAN-4712','DD-MON-YYYY') as gregorian_dt
4        ,dump(to_date('01-JAN-4712','DD-MON-YYYY')) as gregorian_dump
5* from dual
SQL> /

JULIAN_DT            JULIAN_DUMP                       GREGORIAN_DT         GREGORIAN_DUMP
-------------------- --------------------------------- -------------------- --------------------------------
01-JAN-4712 00:00:00 Typ=13 Len=8: 152,237,1,1,0,0,0,0 01-JAN-4712 00:00:00 Typ=13 Len=8: 104,18,1,1,0,0,0,0``````
http://en.wikipedia.org/wiki/Julian_calendar

Edited by: BluShadow on 11-Jan-2013 11:30
• ###### 9. Re: better query
Currently Being Moderated

It's easy to get confused too. Don't think that the year 4712 in the Julian calander is the equivalent of year 4712 in the current gregorian calendar. They're completely different years.
``````SQL> ed
Wrote file afiedt.buf

1  select to_date(1,'J') as julian_dt
2        ,dump(to_date(1,'J')) as julian_dump
3        ,to_date('01-JAN-4712','DD-MON-YYYY') as gregorian_dt
4        ,dump(to_date('01-JAN-4712','DD-MON-YYYY')) as gregorian_dump
5* from dual
SQL> /

JULIAN_DT            JULIAN_DUMP                       GREGORIAN_DT         GREGORIAN_DUMP
-------------------- --------------------------------- -------------------- --------------------------------
01-JAN-4712 00:00:00 Typ=13 Len=8: 152,237,1,1,0,0,0,0 01-JAN-4712 00:00:00 Typ=13 Len=8: 104,18,1,1,0,0,0,0``````
http://en.wikipedia.org/wiki/Julian_calendar

Edited by: BluShadow on 11-Jan-2013 11:30
Blu:
Are you sure they are diferent years? Julian date 1 is 4712 BCE while (unqualified) 4712 is 4712 CE.
``````SQL> alter session set nls_date_format = 'dd-mon-yyyy AD';

Session altered.

SQL> select to_date(1,'J') as julian_dt,
2         dump(to_date(1,'J')) as julian_dump,
3         to_date('01-JAN-4712','DD-MON-YYYY') as gregorian_dt,
4         dump(to_date('01-JAN-4712','DD-MON-YYYY')) as gregorian_dump,
5         to_date('01-JAN-4712 BC','DD-MON-YYYY BC') as gregorian_dt_bce,
6         dump(to_date('01-JAN-4712 BC','DD-MON-YYYY BC')) as gregorian_dump_bce
7  from dual;

JULIAN_DT      JULIAN_DUMP                       GREGORIAN_DT   GREGORIAN_DUMP                   GREGORIAN_DT_B GREGORIAN_DUMP_BCE
-------------- --------------------------------- -------------- -------------------------------- -------------- ---------------------------------
01-jan-4712 BC Typ=13 Len=8: 237,152,1,1,0,0,0,0 01-jan-4712 AD Typ=13 Len=8: 18,104,1,1,0,0,0,0 01-jan-4712 BC Typ=13 Len=8: 237,152,1,1,0,0,0,0``````
John
• ###### 10. Re: better query
Currently Being Moderated
John Spencer wrote:
Blu:
Are you sure they are diferent years? Julian date 1 is 4712 BCE while (unqualified) 4712 is 4712 CE.
You're correct, but how many people are going to be looking at the BCE and CE parts of dates in their output format.
I missed that myself admittedly, but that just indicates how easily we can be confused into seeing a year of 4712 and thinking they may be the same... or different... or whatever. hehe.

Anyway... point well made... it can be confusing.

#### Legend

• Correct Answers - 10 points