SQL & PL/SQL :: How To Do Rounding In 50s Or 100s
Jun 14, 2011
I need to do some analysis and would like to do the rounding/Ceil in 50s or 100s
ex. TABLE_ORDER
ORDER_NUM AMOUNT
1001 22.75
1002 1.75
1003 102.7
1004 240.5
1005 1020.6
1006 99.99
1007 49.99
1008 375
I would like to do Select ORDER_NUM , SOMEFUNCTION(AMOUNT, 50) AS AMT_50, SOMEFUNCTION(AMOUNT, 100) AS AMT_100 from TABLE_ORDER
Result that I am looking
ORDER_NUM__AMT_50____AMT_100
1001________50__________100
1002________50__________100
1003________150_________200
1004________250_________300
1005________1050________1100
1006________100_________100
1007________50__________100
1008________400_________400
View 8 Replies
Sep 24, 2012
I am trying to find some way how to tune and optimize the server performance in following situation. There are 100s of sessions inserting records to one table. Sessions are communication threads in java application, each thread is receiving messages that are to be stored in the table. Each message must be commited and then is ACK sent to remove client. Two problems are raising of course - much of ITLs on the table and lots of very small transactions. I can adjust the java application, but cant do much about the design.
I was thinking about some "caching" - if the messages are stored in memory and bulk-inserted to database by single thread the performance would be much higher. However, there would be possible loss of data - the message could be lost from memory cache and client already received ACK.
View 10 Replies
View Related
Oct 3, 2012
My Oracle DB version is 10g Release2 and also 11gR1
I have data like below, I need to write a SQL for the below requirement.
ID RANGE_LOW RANGE_HIGH
-- ------------------ -------------------
1 50 55
2 55 60
3 60 63
4 63 77
5 77 84
The requirement is like I need to check a value between the above range low and high then round it to the nearest value.
Say, for example if I have 68 then the value is rounded to 63 else if 74 then 77 else if its in the middle of two values (70) then rounded to the highest value, here its 77.
View 4 Replies
View Related
May 21, 2013
rounding down to the nearest 5 using the floor function.My code appears to be correct but I'm not getting the wrong result.
create or replace
TRIGGER "SALE_CALC" BEFORE
INSERT OR
UPDATE ON Prs FOR EACH Row
[code]....
After the trigger executes I get a result of *54.00*, which is the result before the rounding should take place. The result should be *50.00!*
View 5 Replies
View Related
May 29, 2013
I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".
1) create table test18 ( col1 varchar2(10), val Number);
create table succeeded.
2) insert into test18 values ('First', -347026.6408499652467480885711448714129679); -- After decimal 34 digits
1 rows inserted
insert into test18 values ('Second', -347026.64084996524674808857114487141296); -- After decimal 32 digits
1 rows inserted
3) select * from test18;
COL1 VAL
---------- ----------------------
First -347026.6408499652467480885711448714129679
Second -347026.64084996524674808857114487141296
4) As per the requirement, all the columns would need to be concatenated as a single string along with comma delimiter
select col1 || ',' || val as record_string
from test18;
RECORD_STRING
---------------------------------------------------
First,-347026.64084996524674808857114487141297
Second,-347026.64084996524674808857114487141296
"First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place.throw off some light on it and provide the solution how the original record can be kept intact without rounding off.
Expected Output_
RECORD_STRING
---------------------------------------------------
First,-347026.6408499652467480885711448714129679
Second,-347026.64084996524674808857114487141296
View 10 Replies
View Related
