OraFAQ Forum: Forms » How to convert Exponent into pure number

Home » Developer & Programmer » Forms » How to convert Exponent into pure number (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to convert Exponent into pure number [message #204953]

Wed, 22 November 2006 20:01

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?

Report message to a moderator

Re: How to convert Exponent into pure number [message #204956 is a reply to message #204953]

Wed, 22 November 2006 20:53

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: How to convert Exponent into pure number [message #204957 is a reply to message #204953]

Wed, 22 November 2006 21:39

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.

Report message to a moderator

Re: How to convert Exponent into pure number [message #204969 is a reply to message #204957]

Wed, 22 November 2006 23:42

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: How to convert Exponent into pure number [message #204980 is a reply to message #204969]

Wed, 22 November 2006 23:57

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Try

SQL> select to_char(4.40453E+15) from dual;

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

David

Report message to a moderator

Re: How to convert Exponent into pure number [message #205056 is a reply to message #204953]

Thu, 23 November 2006 03:01

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!

Report message to a moderator

Re: How to convert Exponent into pure number [message #205184 is a reply to message #205056]

Thu, 23 November 2006 17:24

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: How to convert Exponent into pure number [message #205187 is a reply to message #204953]

Thu, 23 November 2006 18:02

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.

Report message to a moderator

Re: How to convert Exponent into pure number [message #205197 is a reply to message #205187]

Thu, 23 November 2006 19:08

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Come on!! This isn't hard. Take the 'commas' out of the format clause.

David

Report message to a moderator

Re: How to convert Exponent into pure number [message #205201 is a reply to message #204953]

Thu, 23 November 2006 20:04

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?

Report message to a moderator

Re: How to convert Exponent into pure number [message #205206 is a reply to message #205201]

Thu, 23 November 2006 21:11

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

I repeat:

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

David

Report message to a moderator

Re: How to convert Exponent into pure number [message #205210 is a reply to message #204953]

Thu, 23 November 2006 21:59

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

I already post it, datatype is number size 16, no decimal places

Report message to a moderator

Re: How to convert Exponent into pure number [message #205212 is a reply to message #205210]

Thu, 23 November 2006 22:03

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

Yes, still same result.

Report message to a moderator

Re: How to convert Exponent into pure number [message #205613 is a reply to message #205219]

Sun, 26 November 2006 18:28

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: How to convert Exponent into pure number [message #205614 is a reply to message #204953]

Sun, 26 November 2006 18:43

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

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: How to convert Exponent into pure number [message #205765 is a reply to message #205634]

Mon, 27 November 2006 08:06

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

Report message to a moderator

Re: How to convert Exponent into pure number [message #205822 is a reply to message #205765]

Mon, 27 November 2006 16:12

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: How to convert Exponent into pure number [message #205849 is a reply to message #204953]

Mon, 27 November 2006 22:56

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.

Report message to a moderator

Re: How to convert Exponent into pure number [message #205855 is a reply to message #205849]

Mon, 27 November 2006 23:11

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: How to convert Exponent into pure number [message #205963 is a reply to message #205855]

Tue, 28 November 2006 04:11

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

Attachment: Manipulate_Number_Field_In_Excel.fmb
(Size: 88.00KB, Downloaded 3444 times)

Report message to a moderator

Re: How to convert Exponent into pure number [message #206113 is a reply to message #205963]

Tue, 28 November 2006 17:41

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: How to convert Exponent into pure number [message #206117 is a reply to message #204953]

Tue, 28 November 2006 18:32

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

Thanks so much Saadat. I'll try that.
To all, thanks to your replies.

Report message to a moderator

Re: How to convert Exponent into pure number [message #206125 is a reply to message #204953]

Tue, 28 November 2006 20:07

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.

Report message to a moderator

Re: How to convert Exponent into pure number [message #206135 is a reply to message #206125]

Tue, 28 November 2006 22:22

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: How to convert Exponent into pure number [message #206140 is a reply to message #204953]

Tue, 28 November 2006 22:54

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

Great!
Thanks for that David.

Report message to a moderator

Re: How to convert Exponent into pure number [message #206188 is a reply to message #204953]

Wed, 29 November 2006 02:03

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.

Report message to a moderator

Re: How to convert Exponent into pure number [message #206381 is a reply to message #206188]

Wed, 29 November 2006 18:12

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

What behaviour do you get when you prefix the 16-digit string with a double quote?

David

Report message to a moderator

Re: How to convert Exponent into pure number [message #206619 is a reply to message #204953]

Thu, 30 November 2006 19:26

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.

Report message to a moderator

Re: How to convert Exponent into pure number [message #206620 is a reply to message #206619]

Thu, 30 November 2006 19:34

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Try: equals || double quote || 16-digit number || double quote, that is, ="1234567890123456"

David

Report message to a moderator

Re: How to convert Exponent into pure number [message #206621 is a reply to message #206620]

Thu, 30 November 2006 19:38

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

What is the Number Precision in OpenOffice's spreadsheet?

David

Report message to a moderator

Re: How to convert Exponent into pure number [message #206691 is a reply to message #204953]

Fri, 01 December 2006 02:27

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.

Report message to a moderator

Re: How to convert Exponent into pure number [message #207036 is a reply to message #206691]

Mon, 04 December 2006 00:12

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

If I enter

="1234567890123456"

the number keeps the last digit.

David

Report message to a moderator

Re: How to convert Exponent into pure number [message #207051 is a reply to message #204953]

Mon, 04 December 2006 00:41

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

NaeemAlsaadi
Messages: 20
Registered: November 2010
Location: Oman

Junior Member

nice work it is useful thank you all bro

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 05:51:35 CST 2024