Home » SQL & PL/SQL » SQL & PL/SQL » Rounding off
Rounding off [message #427942] Mon, 26 October 2009 09:10 Go to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Using Toad,
I have 0.21,0.30,0.37,0.13
The sum of these factors are 1.01
But I need to update these values in Column of a table with sum of values = 1 with TWO decimal place.

Pls help me

Thanks in advance
Re: Rounding off [message #427949 is a reply to message #427942] Mon, 26 October 2009 09:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
While I don't understand what the requirement really is, maybe the round function will be of help to you.
Re: Rounding off [message #427952 is a reply to message #427949] Mon, 26 October 2009 09:28 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
We do have TWO decimal place value in the column and the Sum of these values should be equal to 1.00,

But,here We have sum of these values = 1.01,Is it possible to normalise the sum = 1.00(TWO decimal place),in adjustment of any of the four values?

thanks
Re: Rounding off [message #427959 is a reply to message #427952] Mon, 26 October 2009 09:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No.

1.00 = 1 as far as numbers are concerned.

"1.00" would be a string representation of a number in a specific format for display purposes, which could either be done in a client program, or with to_char and the appropriate format mask.
Re: Rounding off [message #427968 is a reply to message #427942] Mon, 26 October 2009 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Rounding off [message #428000 is a reply to message #427952] Mon, 26 October 2009 13:22 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I really do not understand what you are trying to do. Why don't you just subtract .01 from one of your numbers than then you will have 1.0? Other than that, your requirement just sounds silly.
Re: Rounding off [message #428136 is a reply to message #427968] Tue, 27 October 2009 05:59 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michal

create table test ( x float );

insert into test values (0.21);
insert into test values (0.30);
insert into test values (0.37);
insert into test values (0.13);

select sum(x) from test;

Sum(X) --> 1.01

Any one of the values should adjust itself to make Sum(X) = 1.00(TWO DECIMAL) and Update back in X of TEST table

thanks



Re: Rounding off [message #428142 is a reply to message #428136] Tue, 27 October 2009 06:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
SQL> create table test ( x float );

Table created.

SQL>
SQL> insert into test values (0.21);

1 row created.

SQL> insert into test values (0.30);

1 row created.

SQL> insert into test values (0.37);

1 row created.

SQL> insert into test values (0.13);

1 row created.

SQL>
SQL> SELECT * FROM test;

         X
----------
       .21
        .3
       .37
       .13

SQL> SELECT Sum(x) FROM test;

    SUM(X)
----------
      1.01

SQL>
SQL> DECLARE
  2    v_num FLOAT;
  3  BEGIN
  4
  5    SELECT Sum(x)
  6      INTO v_num
  7      FROM test;
  8
  9    UPDATE test
 10       SET x = x - ( v_num - 1)
 11       WHERE  ROWNUM = 1;
 12
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM test;

         X
----------
        .2
        .3
       .37
       .13

SQL> SELECT Sum(x) FROM test;

    SUM(X)
----------
         1

SQL>
Re: Rounding off [message #428169 is a reply to message #428136] Tue, 27 October 2009 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many ways to achieve this depending on your PRECISE requirement.
SQL> select x+decode(rownum,1,1-tot,0) x from test, (select sum(x) tot from test);
         X
----------
        .2
        .3
       .37
       .13

4 rows selected.

SQL> select x/tot from test, (select sum(x) tot from test);
     X/TOT
----------
.207920792
.297029703
.366336634
.128712871

4 rows selected.

SQL> select ratio_to_report(x) over () x from test;
         X
----------
.207920792
.297029703
.366336634
.128712871

4 rows selected.

And so on.

Regards
Michel
Re: Rounding off [message #428185 is a reply to message #428169] Tue, 27 October 2009 07:55 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michal

Thanks for your reply,

Is that possible to find Max(X),and Subtract the difference from

DECODE function?

thanks
Re: Rounding off [message #428201 is a reply to message #428185] Tue, 27 October 2009 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL>  select x+decode(x,max(x) over(),1-tot,0) x from test, (select sum(x) tot from test);
         X
----------
       .21
        .3
       .36
       .13

4 rows selected.

SQL> select x+decode(x,max(x) over(),1-sum(x) over(),0) x from test;
         X
----------
       .21
        .3
       .36
       .13

4 rows selected.

Regards
Michel
Re: Rounding off [message #428260 is a reply to message #428201] Tue, 27 October 2009 11:12 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michael

In the above table test, I am inserting 4 more records

insert into test values(0.11);
insert into test values(0.24);
insert into test values(0.31);
insert into test values(0.33);

alter table test
add y varchar2(2);

update test
set y = 'a'
where x = 0.21;

update test
set y = 'a'
where x = 0.30;

update test
set y = 'a'
where x = 0.37;

update test
set y = 'a'
where x = 0.13;

update test
set y = 'b'
where x = 0.11;

update test
set y = 'b'
where x = 0.24;

update test
set y = 'b'
where x = 0.31;

update test
set y = 'b'
where x = 0.33;

pls find the test case above,

I am trying to update such a way that

sum (0.11,0.24,0.31,0.33) < 1.00
then min (4 values) should be taken and make sum = 1.00
sum (0.21,0.30,0.37,0.13) > 1.00
then max (4 values) should be taken and make sum = 1.00

is it possible to put it in DECODE statement,Pls help me

thanks

[Updated on: Tue, 27 October 2009 11:15] by Moderator

Report message to a moderator

Re: Rounding off [message #428262 is a reply to message #428260] Tue, 27 October 2009 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I showed you many examples to solve this kind of problem, maybe you should make some effort and show us what you tried (without forgetting to use code tags and format your post as I did).

Regards
Michel
Re: Rounding off [message #428344 is a reply to message #428262] Wed, 28 October 2009 00:41 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michal

I am using Toad in Remote desktop,So that I am not able to copy,past the query.Sorry for the inconvenience caused

This is the query I built

select x,x+decode(
x,max(x) over(partition by y),1-sum(x)over(partition by y),
min(x) over(partition by y),1-sum(x) over(partition by y),
0) X from test;

This is not showing the required result,I am not able to use greater than,less than in DECODE statement.

pls help me,Thanks in advance
Re: Rounding off [message #428354 is a reply to message #428344] Wed, 28 October 2009 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For complex condition use CASE instead of DECODE.

Regards
Michel
Re: Rounding off [message #428362 is a reply to message #428354] Wed, 28 October 2009 02:06 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Pls confirm, Can't this query be implemented in DECODE statement?
Re: Rounding off [message #428370 is a reply to message #428362] Wed, 28 October 2009 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it can.

Regards
Michel
Re: Rounding off [message #428456 is a reply to message #428370] Wed, 28 October 2009 07:21 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michal

I have done with Subtraction/ additon using DECODE inside CASE

but,I am now getting problem like

if I have 0.12,0.12,0.41,0.34 records in test table

min(x) = 0.12,It s adding 0.01 for two data and the sum becomes 1.01

at the end, we have 0.13,0.13,0.41,0.34
Sum(X) = 1.01

If we have TWO min/max values,The diffence 0.01/-0.01 should add to the only ONE min value,
Pls advice me how to achieve this

thanks
Re: Rounding off [message #428463 is a reply to message #428456] Wed, 28 October 2009 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case.
Add a number to your row (for instance using row_number) and take the first one, this is one way you can do it.
And post what you found.

Regards
Michel

[Updated on: Wed, 28 October 2009 07:50]

Report message to a moderator

Re: Rounding off [message #428477 is a reply to message #428463] Wed, 28 October 2009 08:27 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michal


Pls find below

0.21,0.06,0.37,0.37

select case
when count(*) over() = count(*) over (order by x asc, rowid)
then x + 1 - sum (x) over (order by x asc,rowid)
else x
end Z
from test;

here,I am not able to use partition by y,when we have multiple records.

thanks
Re: Rounding off [message #428487 is a reply to message #428477] Wed, 28 October 2009 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You forgot:
- new test case
- formatting

Regards
Michel

[Updated on: Wed, 28 October 2009 08:46]

Report message to a moderator

Re: Rounding off [message #428490 is a reply to message #428487] Wed, 28 October 2009 08:52 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michal

I dont have a SQL plus environment here,to display like what you mentioned,Sorry for the inconvienience caused and I m not able to copy it from TOAD in Remote system.

select x,
case
when sum(x) over(partition by y) > 1.00 then
count(*) over(partition by y) = count(*) over (order by x asc, rowid)
x + 1 - sum (x) over (order by x asc,rowid)
when sum(x) over(partition by y) < 1.00
then
count(*) over(partition by y) = count(*) over (order by x desc, rowid)
x + 1 - sum (x) over (order by x desc,rowid)
else x
end Z from test;

thanks
Re: Rounding off [message #428495 is a reply to message #427942] Wed, 28 October 2009 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you've got an oracle client installed, which you'll need to use TOAD, then you'll have sqlplus installed. Have a look for it.
Re: Rounding off [message #428497 is a reply to message #428490] Wed, 28 October 2009 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I asked does not require SQL*Plus, just code tags and indentation in the query (spaces).

Regards
Michel
Re: Rounding off [message #428516 is a reply to message #428497] Wed, 28 October 2009 10:30 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
select x,
case
when sum(x) over(partition by y) > 1.00 then
count(*) over(partition by y) = count(*) over (order by x asc, rowid)
x + 1 - sum (x) over (order by x asc,rowid)
when sum(x) over(partition by y) < 1.00 then
count(*) over(partition by y) = count(*) over (order by x desc, rowid)
x + 1 - sum (x) over (order by x desc,rowid)
else x
end Z from test;

It s throwing error,"Missing Keyword"

Thanks

[Updated on: Wed, 28 October 2009 10:30]

Report message to a moderator

Re: Rounding off [message #428518 is a reply to message #428516] Wed, 28 October 2009 10:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It's missing a keyword somewhere. SQL*Plus would tell you where.
Re: Rounding off [message #428871 is a reply to message #428518] Fri, 30 October 2009 07:28 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
It s working,Thanks for your time and valuable comments
Included partition by y in OVER ()

select x,
case
when sum(x) over(partition by y) > 1.00 then

count(*) over(partition by y) = count(*) over (partition by y order by x asc, rowid)
x + 1 - sum (x) over (partition by y order by x asc,rowid)
when sum(x) over(partition by y) < 1.00 then

count(*) over(partition by y) = count(*) over (partition by y order by x desc, rowid)
x + 1 - sum (x) over (partition by y order by x desc,rowid)
else x
end Z from test;

Thanks
Re: Rounding off [message #428880 is a reply to message #428871] Fri, 30 October 2009 08:23 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you still didn't learn how to correctly format your post.
Next question I will not help if you still refuse to do it.

Regards
Michel
Previous Topic: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL
Next Topic: Oracle trim function not working in trigger (merged)
Goto Forum:
  


Current Time: Fri Dec 02 16:31:05 CST 2016

Total time taken to generate the page: 0.33077 seconds