Home » Developer & Programmer » Forms » How to convert Exponent into pure number  () 1 Vote
How to convert Exponent into pure number Wed, 22 November 2006 20:01
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 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,
Re: How to convert Exponent into pure number [message #205822 is a reply to message #205765] Mon, 27 November 2006 16:12
 Littlefoot Messages: 21123Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
```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,
Re: How to convert Exponent into pure number [message #206113 is a reply to message #205963] Tue, 28 November 2006 17:41
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount Moderator

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
 wency Messages: 450Registered: 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
 wency Messages: 450Registered: April 2006 Location: Philippines Senior Member
Hi,

It's working but rounding off numbers.

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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 wency Messages: 450Registered: April 2006 Location: Philippines Senior Member
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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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
 djmartin Messages: 10180Registered: March 2005 Location: Surges Bay TAS Australia Senior MemberAccount 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
 wency Messages: 450Registered: 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;
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
 NaeemAlsaadi Messages: 20Registered: 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 Jun 26 12:29:31 CDT 2017

Total time taken to generate the page: 0.13498 seconds