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?

 

[sem]

I think that all the data must be stored untouched, no ROUND's or TRUNC's. The a simple view may be created to transform values to whatever you want: either truncated numbers or formatted strings. I hope that so called "tool" is not responsible also for inserting data, thus it doesn't have to use the same object.
Yes, this is a bit more complex, but truncating and rounding are not reversable operations while all requirements are subjected to changes.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

BJ, Dima, Mufasa,

nice to see you all so willing to assist. I guess you've all seen so many problems due to bad decisions about data storage that it really presses the old "ON" button.

Bearing in mind the other thread about storing dates as something other than dates, it amazes me that so many companies out there buy expensive RDBMS technology, employ technologists to use it for them, and then insist that said employees do it wrong, because some manager somewhere thinks they know best.

This seems to be an emerging common thread, amongst all the threads I've read since joining (if you see what I mean).

Maybe this Christmas the real Santa Claus Esquire will bring us a new world of valid, sufficient, consistent data which is full of integrity? Oh yes, and if you're listening Santa, a trouble free version control tool, environment management and fully automatic db backup system would be nice too... sigh.

Regards

Tharg

Grinding away at things Oracular

 

[sem]

Tharg, don't you think that Santa may barter all that gifts for your current job ?

Regards, Dima

http://seminihin.narod.ru

 

[fiat2]

BJCooperIT,
the "tool" has the insert hardcoded, so I cannot update the code.



Thanks all for posting and opening my eyes to how ineffective my "tool" is.


I am just suprised that no datatype will enter a 1.00 as such w/o help from the front end.

 

[SantaMufasa]

Maybe this Christmas the real Santa Claus Esquire will bring us a new world of valid, sufficient, consistent data which is full of integrity? Oh yes, and if you're listening Santa, a trouble-free version control tool, environment management and fully automatic db backup system would be nice too... sigh.

Actually, Tharg, since I'm the only real Santa I know, I've duly noted your wish list for the holidays. I can even promise you that I can (seriously) come through on your wishes...PLUS. There is some amazing software out there that I can fully recommend that does what you ask for plus (the most important part)...it allows you and your colleagues to successfully conduct Rapid Application Development (RAD) of very robust, leading-edge applications in a fraction of the time that it would require using even the best alternatives.

If you want a referral to the provider, contact me via my signature.

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.

 

[Thargy]

Mufasa,

Done.

I hope I got it right, as I clicked on your web link and filled in a ticket.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

Thanks, Tharg...I've responded to your ticket.

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.

 

[LeighRiffel]

I haven't used one before, but it seems like an instead of trigger would be useful here. You could point your application at a veiw of your table rather than the table itself. The view could be defined as follows:

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

You could then use an instead of trigger so that when the application inserts data into the view the trigger does any truncation or rounding necessary and inserts the data into the table.