Home » Developer & Programmer » Forms » How to convert Exponent into pure number  () 1 Vote
How to convert Exponent into pure number [message #204953] Wed, 22 November 2006 20:01 Go to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Hi All,

I have to convert the following
3.3767E+14
4.40453E+15
4.40453E+15

into
337670137917014.00
4404530588226230.00
4404530588226230.00

Any function or formula for this one?
Re: How to convert Exponent into pure number [message #204956 is a reply to message #204953] Wed, 22 November 2006 20:53 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Are these numbers stored this way in a character field or is this the way that you are seeing them being displayed?

David
Re: How to convert Exponent into pure number [message #204957 is a reply to message #204953] Wed, 22 November 2006 21:39 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

These are number type in database and thats's the way I see it. When I get those data thru forms and export it on excel, still it looks the same. The user needs to convert that excel column just to see the long numbers. I need to convert it before exporting.
Re: How to convert Exponent into pure number [message #204969 is a reply to message #204957] Wed, 22 November 2006 23:42 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Excel can 'see' that these numbers are in scientific notation and if you change the column to 'number' then they are displayed correctly.

Please tell me the datatype that exists for this field in the database.

David
Re: How to convert Exponent into pure number [message #204980 is a reply to message #204969] Wed, 22 November 2006 23:57 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Try
SQL> select to_char(4.40453E+15) from dual;

TO_CHAR(4.40453E
----------------
4404530000000000

David
Re: How to convert Exponent into pure number [message #205056 is a reply to message #204953] Thu, 23 November 2006 03:01 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

That won't do david. see the difference on output and the real value of the number .. 4404530000000000 vs 337670137917014.00

Data type in database is number(16)

Yes, the user can format the excel cell and can achieve the desired output. But they're too lazy. Anyway I put my stand that it wasn't possible for now and they have to do it.

Still I have to know this one for future use.
Thanks!
Re: How to convert Exponent into pure number [message #205184 is a reply to message #205056] Thu, 23 November 2006 17:24 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
4404530000000000 and 4404530000000000.00 are the same NUMBER. They are ONLY different if you put them out as a CHARACTER string and to do that use formatting and prefix the value with a double quote.

SQL> select '"'||ltrim(to_char(4.40453E+15,'999,999,999,999,999,990.99')) from dual;

'"'||LTRIM(TO_CHAR(4.40453E+
----------------------------
"4,404,530,000,000,000.00

David
Re: How to convert Exponent into pure number [message #205187 is a reply to message #204953] Thu, 23 November 2006 18:02 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Should I use that, the result of 3.3767E+14 is 337,670,000,000,000.00 instead of 337670137917014.00
This is a card number and must not rounded in thousand.
Re: How to convert Exponent into pure number [message #205197 is a reply to message #205187] Thu, 23 November 2006 19:08 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Come on!! This isn't hard. Take the 'commas' out of the format clause.

David
Re: How to convert Exponent into pure number [message #205201 is a reply to message #204953] Thu, 23 November 2006 20:04 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

But look, digits from 7th position onwards became zero.
Is there any function or type to support large numbers? Or we should have change the type on database to char?
Re: How to convert Exponent into pure number [message #205206 is a reply to message #205201] Thu, 23 November 2006 21:11 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I repeat:
Please tell me the datatype that exists for this field in the database.

David
Re: How to convert Exponent into pure number [message #205210 is a reply to message #204953] Thu, 23 November 2006 21:59 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

I already post it, datatype is number size 16, no decimal places
Re: How to convert Exponent into pure number [message #205212 is a reply to message #205210] Thu, 23 November 2006 22:03 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you tried the "select '"'||ltrim(to_char(my_field,'9999999999999999990')) from dual;" statement on your data?

David

Upd: Removed the decimal places in the format.

[Updated on: Thu, 23 November 2006 22:04]

Report message to a moderator

Re: How to convert Exponent into pure number [message #205219 is a reply to message #204953] Thu, 23 November 2006 23:10 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Yes, still same result.
Re: How to convert Exponent into pure number [message #205613 is a reply to message #205219] Sun, 26 November 2006 18:28 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If that 'select' statement is returning lots of trailing zeroes then I can only assume that you have lots of trailing zeroes in the value in your database. How did the values get stored in the database? Has any other tool 'touched' the records?

David
Re: How to convert Exponent into pure number [message #205614 is a reply to message #204953] Sun, 26 November 2006 18:43 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

I'm not sure David, but as I understand, we only use oracle. I suppose that was automatically converted by oracle because card number happened to be a large number.
What stored in the database were numbers like these:
3.3767E+14
4.40453E+15
4.40453E+15

[Updated on: Sun, 26 November 2006 18:44]

Report message to a moderator

Re: How to convert Exponent into pure number [message #205634 is a reply to message #205614] Sun, 26 November 2006 22:45 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Sorry but you are going to have to find out HOW these numbers were put into the database. We know that Oracle can not store the letter 'E' in a numeric field. Therefore, if the trailing digits have been lost then something in the data entry or maintenance process has done it.

Its too late for the user to complain about missing digits in the report program because all the reporting program is doing is reporting.

David
Re: How to convert Exponent into pure number [message #205765 is a reply to message #205634] Mon, 27 November 2006 08:06 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

Quote:
We know that Oracle can not store the letter 'E' in a numeric field
With due respect, I think you are missing something here.
SQL> CREATE TABLE t
  2  (a_number NUMBER)
  3  /

Table created.

SQL> INSERT INTO t
  2  VALUES(123456789.78654232);

1 row created.

SQL> INSERT INTO t
  2  VALUES(123456789675432.98346);

1 row created.

SQL> INSERT INTO t
  2  VALUES(123456789675432123456789675432.8364735647);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t;

  A_NUMBER
----------
 123456790
1.2346E+14
1.2346E+29


We'll see if we can get a solution for that.

regards,
Saadat Ahmad
Re: How to convert Exponent into pure number [message #205822 is a reply to message #205765] Mon, 27 November 2006 16:12 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This has nothing to do with a problem; your "E" output is dependant on default number format.

In other words, if you did something like this, your theory would fail.
SQL> create table test (a_number number);

Table created.

SQL> insert into test values (123456789675432123456789675432.8364735647);

1 row created.

SQL> set numformat 9999999999999999999999999999999999999.99999999999
SQL> select * from test;

                                          A_NUMBER
--------------------------------------------------
        123456789675432123456789675432.83647356470

SQL>
Re: How to convert Exponent into pure number [message #205849 is a reply to message #204953] Mon, 27 November 2006 22:56 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

I use hiiden field this time with format mask '999999999999999' then I pass the value in it. I can now write the number coorectly to excel, but to prevent excel to format it back to 3.3767E+14, I write single quote before the numbers(eg. char(39) || my_number).
The problem is, the field must be in number format,not in char, so I must omit that single quote. Then I don't know if oracle can format or manipulate excel.
Re: How to convert Exponent into pure number [message #205855 is a reply to message #205849] Mon, 27 November 2006 23:11 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Okay ... we are now back to the original suggestion of getting the users to change the column definition to 'number', or working out how we can do it from Forms. I suggest searching this forum for 'excel' and researching what other people have been able to do with that product. If you write the file as an 'xls' and then open it using MS Excel, I believe that there is a method using either 'ole2' or 'ora_ffi' to set the characteristic on the column.

David
Re: How to convert Exponent into pure number [message #205963 is a reply to message #205855] Tue, 28 November 2006 04:11 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A_NUMBER                                           NUMBER(16)

INSERT INTO t
VALUES(337670137917014)
/
INSERT INTO t
VALUES(4404530588226230)
/
INSERT INTO t
VALUES(4404530588226230)
/
COMMIT
/

SQL> SELECT * FROM t;

  A_NUMBER
----------
3.3767E+14
4.4045E+15
4.4045E+15


Just run these scripts and download the attached form. Run the form. There are two buttons FormToExcel and Form2Excel.
FormToExcel button will show the numbers in scientific notation and Form2Excel will show the result in normal numbers formatting the cell in Excel sheet.

check in the procedures how I manipulated the excel cells in forms.

I hope this will work for you.

regards,
Saadat Ahmad
Re: How to convert Exponent into pure number [message #206113 is a reply to message #205963] Tue, 28 November 2006 17:41 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Saadat,

Thank you very much for posting this example Form which uses 'ole2' to handle communication to and from an Excel spreadsheet.

David
Re: How to convert Exponent into pure number [message #206117 is a reply to message #204953] Tue, 28 November 2006 18:32 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Thanks so much Saadat. I'll try that.
To all, thanks to your replies.
Re: How to convert Exponent into pure number [message #206125 is a reply to message #204953] Tue, 28 November 2006 20:07 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Hi,

It's working but rounding off numbers.
eg:4921611068862010 instead of 4921611068862009

Aside from this, I'm using DDE.POKE to write in excel and not the like of
ole2.set_property(ExcelCellId, 'Value', :t.a_anumber);
.
I'm having trouble trying to adjust my code.
Re: How to convert Exponent into pure number [message #206135 is a reply to message #206125] Tue, 28 November 2006 22:22 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
From Excel help facility:
Excel specifications and limits

Calculation specifications

Feature Maximum limit
Number precision 15 digits
Largest number allowed to be typed into a cell 9.99999999999999E307
Largest allowed positive number 1.79769313486231E308
Smallest allowed negative number 2.2250738585072E-308
Smallest allowed positive number 2.229E-308

It looks like you will have to handle these 16 digit numbers as a character field otherwise Excel with 'ZAP' the 16 digit.

David
Re: How to convert Exponent into pure number [message #206140 is a reply to message #204953] Tue, 28 November 2006 22:54 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Great!
Thanks for that David.
Re: How to convert Exponent into pure number [message #206188 is a reply to message #204953] Wed, 29 November 2006 02:03 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Instead of numberformat like this
OLE2.SET_PROPERTY(ExcelCellId, 'NumberFormat', '#####' );
I need 'text' format. This will solve my problem on 16digits entry. The default of excel is general format.
Somebody here can help me? I can't find any in internet.
Re: How to convert Exponent into pure number [message #206381 is a reply to message #206188] Wed, 29 November 2006 18:12 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What behaviour do you get when you prefix the 16-digit string with a double quote?

David
Re: How to convert Exponent into pure number [message #206619 is a reply to message #204953] Thu, 30 November 2006 19:26 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Double quote won't do it, I use single qoute using chr(39). The output is '1234567898765432. That is correct but I have to omit that quote. The only way I think is to format the excel cells into text so that anything I write into it will displat as is.
Re: How to convert Exponent into pure number [message #206620 is a reply to message #206619] Thu, 30 November 2006 19:34 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Try: equals || double quote || 16-digit number || double quote, that is, ="1234567890123456"

David
Re: How to convert Exponent into pure number [message #206621 is a reply to message #206620] Thu, 30 November 2006 19:38 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What is the Number Precision in OpenOffice's spreadsheet?

David
Re: How to convert Exponent into pure number [message #206691 is a reply to message #204953] Fri, 01 December 2006 02:27 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Using numberformat, the numbers were correct up to 15th digit, the last one is always zero. Even if I manually insert it to excel, it has same result.
Re: How to convert Exponent into pure number [message #207036 is a reply to message #206691] Mon, 04 December 2006 00:12 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If I enter
="1234567890123456"
the number keeps the last digit.

David
Re: How to convert Exponent into pure number [message #207051 is a reply to message #204953] Mon, 04 December 2006 00:41 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Yes David, it will return the correct numbers. But my boss don't want that formula seen on top.
Anyway, I solved my problem on this and I want to share to everyone here. I format the third comlumn on excel to make it text so it can display what entered as is.
cols := OLE2.GET_OBJ_PROPERTY(application, 'Columns');
ExcelArgs := OLE2.CREATE_ARGLIST;  
OLE2.ADD_ARG(ExcelArgs, 'C:C');  
rng := OLE2.GET_OBJ_PROPERTY(cols, 'Item', ExcelArgs);  
OLE2.DESTROY_ARGLIST(ExcelArgs);
OLE2.SET_PROPERTY(rng, 'NumberFormat', '@'); --text format


Thanks David and to all participated in this thread.
Cheers!

[Updated on: Mon, 04 December 2006 00:43]

Report message to a moderator

Re: How to convert Exponent into pure number [message #483560 is a reply to message #207051] Mon, 22 November 2010 22:57 Go to previous message
NaeemAlsaadi
Messages: 20
Registered: November 2010
Location: Oman
Junior Member
nice work it is useful thank you all bro
Previous Topic: how 2 share PL/SQL variable between 2 forms
Next Topic: Hierarchy tree - how to differentiate selected that highlighted to non-highted
Goto Forum:
  


Current Time: Mon Dec 05 19:25:26 CST 2016

Total time taken to generate the page: 0.10638 seconds