PL/SQL :: SQL For Rounding To Nearest Value
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
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
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
Nov 1, 2012
I am facing small issue, i need to round the number to the nearest 10k for some finance reporting
ex
1. if number is 2250 round to 10,000
2. if number is 5500 round to 10,000
3. if number is 4912345 round to 4920000
etc..
the number should always round up to nearest 10k..
I am using round function to achieve this..
case 1:-
SQL> Select round(2250,-4) From dual;
ROUND(2250,-4)
--------------
0
I need 10,000 here..
case 2:-
SQL> Select round(5500 ,-4) From dual;
ROUND(5500,-4)
--------------
10000
SQL>
This is working fine...
case 3:-
SQL> Select round(4912345 ,-4) From dual;
ROUND(4912345,-4)
-----------------
4910000
This should round UP to 4920000
let me know if there is any way i can achieve this...
View 10 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
May 24, 2012
I want to find nearest integer value where mod returns 0 in sql statement. I've tried following but it doesn't fulfill my requirement.
My Try
SQL> select
2 ((1200*1000)+45-mod((1200*1000),45)) f1,
3 mod( ((1200*1000)+45-mod((1200*1000),45)),45 ) f2,
4 ((1200*1000)+45-mod((1200*1000),45))/1000 f3
5 from dual;
F1 F2 F3
---------- ---------- ----------
1200015 0 1200.015
In above result F3 represent the actual result, which is nearest value where mod returns the 0, but i want nearest integer value which is 1206. how it is possible. In above case consider 1200 as Kgs and 45 as Grams.
View 20 Replies
View Related
Jun 1, 2011
Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.
Miss
Mississ
Mississipp
I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)
If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.
create table names (name varchar2(20));
insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;
View 2 Replies
View Related
Oct 13, 2013
I am using Oracle 12c Spatial and Graph and I would like to retrieve the nearest neighbors of a point from a dataset encoded according to GeoSPARQL.I have created a spatial index on the datatype geo:wktLiteral and I to the query so the optimizer pick a plan using the index RDF_V$GEO_IDX. The query I pose is the following:
SELECT geo, wkt
FROM TABLE(SEM_MATCH('
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
SELECT ?geo ?wkt
[code]...
I do not get any error but I do not get any results neither. However, there are a lot of points around POINT(22.39 38.25) in my dataset and if I remove the filter I get results.Do I use orageo:nearestNeighbor in a wrong way?
View 6 Replies
View Related
