Home » SQL & PL/SQL » SQL & PL/SQL » Can values of rows being queried dynamically altered even as they are queried?
Can values of rows being queried dynamically altered even as they are queried? Tue, 21 February 2006 16:09
 rasa Messages: 45Registered: February 2006 Member
A single-column table has values like this:

3.33
6.46
1.22

I want to do the following rounding-off logic (it is not a simple ROUND() as it may sound) by carrying-forward the decimal portion bucket to subsequent rows to round them based on the carrying-forward of the decimal portions:

3 (our fraction bucket has extra 0.33 to apply for next row)
7 (0.46+0.33 = 0.79 and that is rounded upwards to 7. But there is a shortage of 0.21 in our fraction bucket to achieve this.)
1 (here the decimal is 0.22 and we will have to deduct the extra 0.21 we applied in the previous row and that brings to 0.01 and so we round downwards to 1 and discard the extra 0.01 we have in our fraction bucket).

The rounding logic goes like this. I have achieved this with PL/SQL, but am wondering if this is possible to achieve in SQL. The more I think about this it has to do with altering the values of subsequent rows depending upon the values of the earlier rows being queried. Is it possible to do it?

Re: Can values of rows being queried dynamically altered even as they are queried? [message #159820 is a reply to message #159817] Tue, 21 February 2006 17:01
 Art Metzer Messages: 2478Registered: December 2002 Senior Member
Using analytic functions, yes, it is possible.
```SQL> COLUMN amount          FORMAT  90.00
SQL> COLUMN fraction_bucket FORMAT S90.00
SQL> CREATE TABLE t (id NUMBER, amount NUMBER)
2  /

Table created.

SQL> INSERT INTO t VALUES (1, 3.33);
SQL> INSERT INTO t VALUES (2, 6.46);
SQL> INSERT INTO t VALUES (3, 1.22);
SQL> INSERT INTO t VALUES (4, 5.10);
SQL> INSERT INTO t VALUES (5, 1.10);
SQL> INSERT INTO t VALUES (6, 3.09);
SQL> INSERT INTO t VALUES (7, 8.10);
SQL> INSERT INTO t VALUES (8, 7.10);

SQL> SELECT   a.id
2  ,        a.amount
3  ,        a.running_tally
4           - ROUND(a.running_tally)             fraction_bucket
5  ,        DECODE(SIGN(a.running_tally
6                       - ROUND(a.running_tally))
7           ,      -1, 1
8           ,      0) + FLOOR(a.amount)          rounded_a_la_rasa
9  FROM    (SELECT   t.id
10           ,        t.amount
11           ,        SUM(t.amount)
12                    OVER (ORDER BY t.id)        running_tally
13           FROM     t)                          a
14  ORDER BY a.id
15  /

ID AMOUNT FRACTION_BUCKET ROUNDED_A_LA_RASA
---------- ------ --------------- -----------------
1   3.33           +0.33                 3
2   6.46           -0.21                 7
3   1.22           +0.01                 1
4   5.10           +0.11                 5
5   1.10           +0.21                 1
6   3.09           +0.30                 3
7   8.10           +0.40                 8
8   7.10           -0.50                 8

8 rows selected.

SQL>```
Re: Can values of rows being queried dynamically altered even as they are queried? [message #183968 is a reply to message #159820] Mon, 24 July 2006 10:24
 rasa Messages: 45Registered: February 2006 Member
Art:

Thanks for the pointer. However, the requirement is different than the logic shown:

```Insert into t
(ID, D, AMOUNT)
Values
(1, TO_DATE('07/01/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1374.718);
Insert into t
(ID, D, AMOUNT)
Values
(1, TO_DATE('07/02/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1031.059);
Insert into t
(ID, D, AMOUNT)
Values
(1, TO_DATE('07/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 980.741);
Insert into t
(ID, D, AMOUNT)
Values
(1, TO_DATE('07/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1028.176);
Insert into t
(ID, D, AMOUNT)
Values
(1, TO_DATE('07/05/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1356.106);
COMMIT;
```

The output should be as shown below.

```Actual	        ROUND	Desired	Fraction_Tally
1374.718	1375	1374	0.718
1031.059	1031	1031	0.777
980.741	        981	981	0.518
1028.176	1028	1028	0.694
1356.106	1356	1356	0.8
```

Let's have a separate bucket to tally the "fraction" or "remnants."

1374.718 must be 1374 and should not be rounded off to the higher decimal TO START WITH because it has a deficit of -0.282 to upgrade. After it is set to 1374, it has a surplus of +0.718 in the remnant bucket.

Next, 1031.059 must still be 1031 because the remnant bucket with 0.059 added will yield +0.777 (0.718 + 0.059 = 0.777). Since the remnant bucket is < 1, it is still 1031 and not 1032.

Next, 980.741 must be upgraded to 981, because the remnant bucket will be > 1 if you add the portion of 0.741 to the already surplus 0.777 in the remnant bucket. But upgrading it to 981, will ensue a debit of 0.259. So, by upgrading 980.741 to 981, the current remnant bucket score lowers from 0.777 to 0.518 (0.777 - (1.000-0.741) => 0.777 - 0.259 = 0.518)

So on and so forth...

Unfortunately, the posited logic rounds-off numbers without paying attention to the "credit/debit" logic of the "remnant bucket." This is what it produces:

```ID	D	        AMOUNT	       FRACTION_BUCKET	          ROUNDED_A_LA_RASA
1	7/1/2006	1374.718	-0.282	          1375
1	7/2/2006	1031.059	-0.223	          1032
1	7/3/2006	980.741	        -0.482	          981
1	7/4/2006	1028.176	-0.306	          1029
1	7/5/2006	1356.106	-0.2	          1357
```

The ROUNDED_A_LA_RASA column values DO NOT match the "DESIRED" column values posited above.

I tried analytic queries like LAG, SUM OVER, but couldn't handle this complex logic within SQL. I have a working solution in PL/SQL, but I want this in SQL.

Re: Can values of rows being queried dynamically altered even as they are queried? [message #184010 is a reply to message #183968] Mon, 24 July 2006 21:07
 zozogirl Messages: 77Registered: November 2005 Location: Seoul, Korea Member
Maybe, this is what you want.

```SELECT actual
, ROUND
,   desired#
+ DECODE (TRUNC (f_t)
, TRUNC (LAG (f_t, 1, f_t) OVER (ORDER BY 1)), 0
, 1
) desired
, MOD (f_t, 1) fraction_tally
FROM (SELECT amount actual
, ROUND (amount) ROUND
, TRUNC (amount) desired#
, SUM (MOD (amount, 1)) OVER (ORDER BY ROWNUM) f_t
FROM t)

ACTUAL      ROUND    DESIRED FRACTION_TALLY
---------- ---------- ---------- --------------
1374.718       1375       1374           .718
1031.059       1031       1031           .777
980.741        981        981           .518
1028.176       1028       1028           .694
1356.106       1356       1356             .8
```

Query Your Dream & Future at SoQooL
http://www.soqool.com
Re: Can values of rows being queried dynamically altered even as they are queried? [message #184484 is a reply to message #184010] Wed, 26 July 2006 14:39
 rasa Messages: 45Registered: February 2006 Member
Thanks a lot, zozogirl. That works!
 Previous Topic: Find out the type of a variable or of a column in a table Next Topic: Multi step procedure query plans
Goto Forum:

Current Time: Tue Aug 22 21:36:13 CDT 2017

Total time taken to generate the page: 0.03845 seconds