PL/SQL :: Round The Number To Nearest 10k

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


ADVERTISEMENT

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 View Related

SQL & PL/SQL :: How To Find Nearest Integer / Where Mod Returns 0

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

SQL & PL/SQL :: How To Find Nearest / Closest String For A Column Data

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

Semantic Technologies :: Orageo Nearest Neighbor No Results?

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

How To Round Time Up Or Down By 5 Minutes

Nov 29, 2006

is there a prebuilt function that will round say the time of a sysdate up or down 5 mins? so i entered 5:32pm i would want it to round it down to 5:30pm

View 1 Replies View Related

SQL & PL/SQL :: Round Robin Implementation Through Script?

Aug 19, 2010

I have to implement round robin algorothim in sql script for allocation of some items to another entity. Not really sure how to do that and if there would be locks while selecting the one which is already being updated and so...!

how to go about it when the number of users involved would be very high to whom the items would be allocated in round robin function.

View 15 Replies View Related

PL/SQL :: Unable To Round Date Function?

Feb 21, 2013

cant i round the date like this? or only sysdate can be rounded or if the date is stored in a variable can only be rounded

select round('21-feb-1999','mon') from dual;

View 2 Replies View Related

PL/SQL :: Function To Round To Previous Quarter

Feb 28, 2013

I need to have a function which will round to previous quarter

For ex :

if the value is 1.95 it should give 1.75
if the value is 1.60 it should give 1.50

View 2 Replies View Related

Performance Tuning :: Px Send Round-robin?

Jun 19, 2012

what is "px send round-robin "

View 1 Replies View Related

SQL Net Round Trips On Long Data Type Table

Oct 12, 2012

We are working on a performance tuning aspect, where in a table has a LONG datatype and number of round trips are increasing based on the number of rows fetched.i.e one round trip for every row.

Background.

1. created a table with LONG data type.
2. inserted bulk load of data.
3. set auto trace on and executed below tests..

SQL> select count(*) from test_long;

COUNT(*)
----------
6110

SQL> desc test_long
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NUMBER
TESTLONG LONG
[code].....

but on LONG column, irrespective of the array size the round trips does not reduce.

View 1 Replies View Related

PL/SQL :: Round Function Output Depend On 2nd Digit After Decimal Point

Oct 19, 2012

I have a question on Round Function

Round(###.###,1)

Will the round function output depend on 2nd digit after decimal point also or not?

View 6 Replies View Related

SQL & PL/SQL :: Time Calculation - Make Varchar To Timestamps But Caught With Round Up / Down?

Apr 12, 2011

calculating values of A & B

(both these fields a_std and a_time are coming as varchar from the parent table in a cursor.(basically they are time period and actual arrival time respectively)

i was juggling with the attempt to make varchar to timestamp or date..but caught with Round up /Round down)

Formula ->

A = Round down [A_TIME - A_STD]
B = Round up [A_TIME) - 10 minute + A_STD]

where

A_TIME VARCHAR2(8) N Time (Format" HH:MM AM/PM") eg "3:50 PM"
A_STD VARCHAR2(5) N Standard time (Format" HH:MM") eg "1:00"

Allowed values for A & B after round up/down = multiple of 10 ( 11:00,11:10,11:20 etc.)

View 10 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

SQL & PL/SQL :: Replacing 4 Digit Number In A Given String With The Same Number Incremented By 10000?

Jun 17, 2010

i want to replace 4 digit number in a given string with the same number incremented by 10000.

That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).

Input String:

<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

Required output :

<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

View 7 Replies View Related

SQL & PL/SQL :: Extract Number And Previous Character From Where 5 Digit Number Starting

Oct 21, 2011

I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting

For example i/p asdfasfsdS251432dasdasd o/p should be S251432

View 10 Replies View Related

Express Edition (XE) :: Why Maximum Number Of Voting Disk Is Even Number (32)

Oct 3, 2013

I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?

View 1 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

SQL & PL/SQL :: How To Generate Number (not Sequence Number) In Query

Mar 9, 2011

I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).

the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?

select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25

View 4 Replies View Related

PL/SQL :: Insert Number Into Column With Type  NUMBER(10,0)

Sep 28, 2012

my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?

View 6 Replies View Related

SQL & PL/SQL :: Number Generation MINUS Number Used

Feb 19, 2011

Here is script of tables

Quote:drop table p;
create table p (qty number(3), beg_no number(5));
insert into p values(5, 110);
insert into p values(8, 786);

drop table s;

create table s (used_no number(5));
insert into s values(111);
insert into s values(113);
insert into s values(791);

Table p: it has ticket quantity and ticket begining number. Thus according to first record ticket number will begin at 110 and will end at 110+5 (Beg_no +qty). According to second record ticket number will begin at 786 and will end at 786+8 (Beg_no +qty). This table can have many records.

Table s: it has ticket numbers which are sold. The ticket will always be any number from table and will lay in any record in this format between beg_no and beg_no+qty

Required: I want "p MINUS s" information. i.e.

Quote:
110
112
114
786
787
788
789
790
792
793

I am not expert in level by command.

Oracle version: 9
OS: Windows XP

View 3 Replies View Related

Reports & Discoverer :: How To Compare Current Page Number With Total Page Number

Feb 19, 2013

I have a report created in Reports6i. I have two fields at the bottom

1.Page Total
2.Voucher Total

I do not want to print the Page Total if the total number of pages = 1.

How can I achieve this?

View 1 Replies View Related

SQL & PL/SQL :: Add Number Containing (,)?

Apr 19, 2010

i have field(column name ) amata type is number . in excel i am having "," in amount field eg 9,999. how to do it

View 6 Replies View Related

SQL & PL/SQL :: How To Get Serial Number

Mar 12, 2013

I have following table.

CREATE TABLE ABC
(
DPT_NUM NUMBER,
LOT_NUM NUMBER,
ASSOCIATED_WITH_LOT NUMBER
);
SET DEFINE OFF;
Insert into ABC
[code]......

Now i run follwoing query and result is

select * from abc
DPT_NUMLOT_NUMASSOCIATED_WITH_LOT

1501
1502501
1509501
1511
1503
1516
1522
1565
1569565
2601
2602
2604602
2607
2508

I need following result

DPT_NUMLOT_NUMASSOCIATED_WITH_LOT Serial_Number

1501 1
1502501 2
1509501 2
1511 3
1503 4
1516 1
1522 2
1565 3
1569565 3
2601 1
2602 2
2604602 2
2607 3
2508 3

Note that serial number must reset after each 4 lot_num againt dpt_num. Not that where lot is associated in associated_with_lot there serial number must be same as serial number is for associated lot.

View 7 Replies View Related

SQL & PL/SQL :: How To Convert RAW To Number

Sep 27, 2013

can we convert RAW to number in sql.because i have to bitand of one raw and number variable.

View 3 Replies View Related

SQL & PL/SQL :: Reset Row Number

Mar 1, 2013

I have Follwoing Table and data.

CREATE TABLE ABC(DPT_NUM NUMBER,LOT_NUM NUMBER);
Insert into ABC(DPT_NUM, LOT_NUM, SRL_NUM) Values (1, 501, 1);
Insert into ABC(DPT_NUM, LOT_NUM, SRL_NUM) Values (1, 502, 2);
Insert into ABC(DPT_NUM, LOT_NUM, SRL_NUM) Values (1, 509, 3);
Insert into ABC(DPT_NUM, LOT_NUM, SRL_NUM) Values (1, 511, 4);
Insert into ABC(DPT_NUM, LOT_NUM, SRL_NUM) Values (1, 503, 5);

[Code]...

I write Following query.

SELECT DPT_NUM,LOT_NUM,ROW_NUMBER() OVER(PARTITION BY DPT_NUM ORDER BY DPT_NUM) SRL_NUM FROM ABC;

Result is

DPT_NUMLOT_NUMSRL_NUM
15011
15022
15093
15114
15035
15166
15227
15698

[Code]...

But i want to reset rownum after each 4. Mean after serial number 4 , serail number must start from 1. I want to reset serial number after each 4 records (lot_num) against dpt_num;

View 7 Replies View Related

SQL & PL/SQL :: Number Conversion

Jan 4, 2013

I have a table datatype number (12,10) that I am reading out of. I am taking the value from this source table and inserting it into a destination table of datatype number (12,15).

I do not have the ability to alter the tables. How can i convert this number so i can insert. I am currently getting the error "ORA-01438: value larger than specified precision allowed for this column"

I am trying to use the to_number, but it not working. How can i format this number field so i can read it from source where i have number (12,10) and insert it successfully in a higher precision table of number(12,15)

View 1 Replies View Related

SQL & PL/SQL :: Number Of Occurrence

Oct 29, 2010

I'm trying to find a way to see if a value occurs more than once in a string. I just need to know "T/F", or "Y/N", etc.

The string will be comma delimited.

String: '1,2,3,1'
Ans: "T"

String: '1,2,3,4'
Ans: "N"

They do need to match exactly. for instance

String: '1,2,3,1a'
Ans: "N"

Using some code I found on this site, I coded this but I'm sure there's a better way. Is there??

--Check for Duplicate combination row values
BEGIN
--Query will split the string into individual pieces.
--Group the pieces to see if any 2 rows are the same
--If no rows are the same then "no_data_found" exception is thrown
SELECT 'T'
INTO vResult

[Code]...

View 10 Replies View Related

SQL & PL/SQL :: How To Know Previous Scn Number

Jun 4, 2012

i want to know the all scn number's that are generated yesterday or in any previous day? how can i achieve it?

View 5 Replies View Related

PL/SQL :: Convert Number?

Jun 14, 2013

would need to convert a number in this way:10.1 ---> 10101.90 ----> 190  How can i?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved