Oracle Datatype question. 2

[fiat2]

This seems like a simple request but I was unable to find any thing on Internet

I have a Oracle 9i database.

User would like to enter number as a 10 and be stored as 10.00.

Also, User would like to enter number as 10.556 and be stored as 10.55, not 10.56.

Stumped here.
any help?

 

[SantaMufasa]

Fiat,

First, why is the user dictating internal storage formats to developers? Are the users "Developer Wannabes"?

Second, algebraically, "10" and "10.00" represent the same value. If you are storing "10" and the user wants to see "10.00", then that is an output-formatting issue; it should not be an internal-storage issue. If you want to store "10.00", then you must store it as VARCHAR data...a bad storage-efficiency issue since it will take 250% more storage space to store character "10.00" than numeric 10.

If you choose to store "10.556" as "10.55", then you can use a variation of this code:

SQL> select trunc(10.556,2) from dual;

TRUNC(10.556,2)
---------------
          10.55

Let us know if any of this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[fiat2]

The users want to store all data the same format, since if in the future they need to pull data, it will all be in the same format, 10.00 and 8.55, rather than 10 and 8.5..


We are using a 3rd party tool to display the data from database in a grid. We are limited in what we can do to the data being pulled. I will give your second option a try.

I was under the impression that a certain datatype could force 10 to 10.00?

thank you Santa,

 

[SantaMufasa]

Fiat,

If your "3rd party tool" cannot format a "10" to "10.00", then it is not really a "tool". Worthy tools should not force you to degrade internal efficiencies. Sounds like your "tools" need to change to fit Oracle rather than your degrading Oracle to fit your tool.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[fiat2]

Does that mean Oracle cannot format this then?

 

[SantaMufasa]

Absolutely NOT...Oracle has very strong formatting capabilities, especially for NUMBER and DATE columns. Oracle lets you format your values in just about any manner you please. If you give us your desired output format, we can post the edit mask to output what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

Oracle can format numbers and dates in a wealth of ways. The point is: a number is only a number.

10 = 10.0 = 10.00 = 10.000 and so on.

There is only one accptable way to store it - as a number with optional precision for integers and decimal points.

A number can be formatted, edited, rounded, truncated, etc. in any fashion by most reputable software.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[fiat2]

Well, then.
Thank you for your opinion on our "tools". Eventhough I did not ask for one.

I did ask if I can format a 10 to a 10.00 and you say no but then you say Oracle can format NUMBER columns, well.

I feel that I was clear in what I was having trouble in.

10 = 10.00
and
10.556 = 10.55


FIAT

 

[BJCooperIT]

Your trouble does not lie in the capabilities of storing data in Oracle. There was no offense intended, but you were not clear as to the datatype of the field in your database. Is it a number or a varchar2? If it is a number, the front end needs to apply a mask.

Number fields can be defined such as:

AMT number;
AMT1 number(8);
AMT2 number(8,2);

The way it gets displayed is up to the software retrieving the data. For instance, if AMT2 had a value of 1234.5, it could be displayed as 1,234.50 or $1,234.50, etc.

If it is a varchar2 then the data is stored in a fashion that breaks most standards.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[fiat2]

I realize that now, but his posts came off strong and not to any of my points.

I have not decided what type of datatypes to store these values. Sortof looking for suggestions.


Thanks

 

[SantaMufasa]

Fiat,

Barb is correct...I intended no offense to you...just the opposite...My words were to reinforce your position of going to your Third-Party Provider and asking them for reasonable front-end formatting capability.

Barb and I would be pleased to offer you SQL code to produce the effect you want if you would, as Barb suggested, confirm the datatype declarations of your data that you would like to format.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[fiat2]

Glad to see we are all on the same page now.
I'm am sorry if my words offended.

The current table is empty. I can declare the fields any type I want to now. I will choose which ever will help me accomplish my goals.

Going to the vendor is not an option right now, but will definitly keep this in mind when we do.

THanks

 

[BJCooperIT]

I think we have stated our case here for your suggestion - numeric data is stored in a number field, not in a text string. Precision (number of integers and decimal points) is optional. Personally, I rarely specify precision due to the maintenance issues.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[fiat2]

Currently, I have a test table with 2 columns
CREATE TABLE TEST_TAB(
STAT VARCHAR2 (10) NOT NULL,
VARASTAT NUMBER (4,2) NOT NULL ) ;


When I perform this insert:
insert into TEST_TAB values
('SET', 2.00);

and then this
select * from TEST_TAB;

I get this:
SET, 2


Obviously, I am not going to run for Mr. Oracle DBA but I am surprised that I am having this much trouble storing this value and retrieving it.

thank you.

 

[BJCooperIT]

SQL> CREATE TABLE TEST_TAB(
  2    STAT     VARCHAR2 (10)   NOT NULL,
  3    VARASTAT NUMBER   (4,2)  NOT NULL ) ;

Table created

SQL> 
SQL> insert into TEST_TAB values
  2  ('SET', 2.00);

1 row inserted

SQL> 
SQL> select STAT, to_char(VARATAT,'90.00') varastat from TEST_TAB;

STAT       VARASTAT
---------- --------
SET          2.00

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[fiat2]

That worked great.

Now I am greedy, I need to either prevent user from entering a number with 3 places after decimal or just cut it off.


10.556 = 10.55

?

Thanks,

 

[BJCooperIT]

How is the user entering the data? SQL*Plus? Oracle Forms?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[fiat2]

We have a tool that provides an interface to the oracle 9i database. The tool was designed in vb6.

The tool allows us to create a sql statement to populate the grid but the menus that we create, have hardcoded insert statements.

I was ahead of myself. I can perform the select statement that Santa provided earlier:

select trunc(10.556,2) from dual;


But I cannot control how the data gets entered, a mask or even constraints on the fields, i.e size.

Thank you all for your help.

 

[BJCooperIT]

If you are hard-coding your INSERT statements then you can also truncate there.

insert into TEST_TAB values ('SET', trunc(&user_amt,2));

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[BJCooperIT]

I should have pointed out that this is a bit user-UNfriendly since you are truncating user data without informing them. You could use the ROUND function instead to insert the closest amount to what they entered.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus