Home » SQL & PL/SQL » SQL & PL/SQL » number to word conversion.
number to word conversion. [message #237965] Wed, 16 May 2007 03:54 Go to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
I would like to know how the query works.It's for number to word conversion.

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )from dual[/B]
Re: number to word conversion. [message #237969 is a reply to message #237965] Wed, 16 May 2007 03:58 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
It uses the Julian date format, which is basically a number. The 'Jsp' masks gives the date in 'spoken' language.

Regards,

Arian
Re: number to word conversion. [message #237987 is a reply to message #237969] Wed, 16 May 2007 05:13 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
How the query works,and what is jullian day concept in oracle

select
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
Re: number to word conversion. [message #237989 is a reply to message #237987] Wed, 16 May 2007 05:18 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Hi,

Julian date: http://en.wikipedia.org/wiki/Julian_date

As for the query:
SQL> select decode(sign(12345),-1,'Negative',0,'Zero',NULL) from dual;

D
-


SQL> select decode(sign(-12345),-1,'Negative',0,'Zero',NULL) from dual;

DECODE(S
--------
Negative

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

TO_DATE(
--------
19-10-79

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

TO_CHAR(TO_DATE(12345,'J'),'JSP')
----------------------------------------
Twelve Thousand Three Hundred Forty-Five

SQL> 


Which part of the query you don't understand?

Regards,

Arian
Re: number to word conversion. [message #237995 is a reply to message #237969] Wed, 16 May 2007 05:36 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
The query is not working for the input 1721058.Plz explain why? What should i do to overcome this limitations?
Re: number to word conversion. [message #237997 is a reply to message #237989] Wed, 16 May 2007 05:40 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
Please explain these two parts written bellow:-

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

2>select to_char(to_date(12345,'J'),'Jsp') from dual;

I don't understand how these two works.Plz explain.
Re: number to word conversion. [message #238018 is a reply to message #237997] Wed, 16 May 2007 06:23 Go to previous message
Alien
Messages: 245
Registered: June 1999
Senior Member
Hi,

see:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1407603857650
for your first question.

the
to_date(12345,'J')

converts the number into a date format. Since it is a number we tell Oracle that the number is a julian date that we want to convert to the Oracle date-format.

From the Oracle date format, we can convert using the 'Jsp' mask. That tells Oracle to spell out the date as a number.

Regards,

Arian
Previous Topic: selection from more than two tables
Next Topic: merge statement PL/SQL: ORA-00933: SQL command not properly ended
Goto Forum:
  


Current Time: Sat Dec 03 07:57:46 CST 2016

Total time taken to generate the page: 0.10894 seconds