Solved

# Can Oracle SQL use data you calculated on a previous row, in the current row?

Posted on 2011-10-17
649 Views
Can Oracle SQL use data you calculated on a previous row, in the current row?

This is NOT by referencing real columns like in the LAG analytics statement, but by referencing the calculated, pseudo column's previous row.

This is a very common problem which Excel users often do by saying add a neighbouring column's value to the row above.
0
Question by:hydroIT

LVL 76

Expert Comment

I'm not sure what you are asking.  Why won't LAG do what you need?

Sample data and expected results would help a lot.

Depending on what you are after, in 11g, you can create virtual columns which are derived at access.

Possibly you are after a combination of the two?
0

Author Comment

Here's some expected results:

rn      A      B      calculated
1      2      3      =RC[-1]+R[-1]C
2      5      6      =RC[-1]+R[-1]C
3      3      7      =RC[-1]+R[-1]C

As replicated by:

select *
from
(
select rownum,  2 as A ,3 as B, '3' as calculated from dual
union
select 2,  5 , 6, '6+3' from dual
union
select 3,  3, 7, '(6+3) + 7' from dual
)
0

LVL 76

Expert Comment

I don't get what "=RC[-1]+R[-1]C" means.

From the sql, it looks like you are wanting a running sum on column 'C'.
0

Author Comment

=RC[-1]+R[-1]C

would be (same row, one column to the left) + (row above, same column)
=> creates a running total

Yes, the SUM analytic can do running totals but I need to do logic on the row above as well ie use the previous row's, calculated column's value.
0

LVL 15

Expert Comment

select a,b,calculated,SUM(SUM(b)) OVER
(ORDER BY rownum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sql_calc
from
(
select rownum,  2 as A ,3 as B, '3' as calculated from dual
union
select 2,  5 , 6, '6+3' from dual
union
select 3,  3, 7, '(6+3) + 7' from dual
)
group by a,b,calculated,rownum order by rownum
0

LVL 76

Expert Comment

Not sure why you need all the between and unbounded.

I appear to get the same results with a simple SUM
``````with mydata as (
select 1 as myrownum, 2 as A ,3 as B, '3' as calculated from dual
union
select 2,  5 , 6, '6+3' from dual
union
select 3,  3, 7, '(6+3) + 7' from dual
)
select a, b, calculated, sum(b) over(order by myrownum) from mydata
/
``````
0

LVL 73

Accepted Solution

I agree analytics are sufficient for this specific example,
but if you're looking for "cell" type references. perhaps you are looking for  the model clause

``````SELECT *
FROM (SELECT ROWNUM rn, 2 AS a, 3 AS b FROM DUAL
UNION
SELECT 2, 5, 6 FROM DUAL
UNION
SELECT 3, 3, 7 FROM DUAL)
MODEL
DIMENSION BY(rn)
MEASURES(b, 0 calculated)
RULES
(calculated [1] = b[1],
calculated [rn > 1] = calculated[CV(rn) - 1] + b[CV(rn)])
``````
0

LVL 3

Expert Comment

select rn, a, b, sum(b) over (order by rowid) as calculated from t1
0

LVL 76

Expert Comment

gajmp,

Isn't that basically what I posted in http:#a36985543

Also, rowid isn't a great column to use for sorting.
0

LVL 73

Expert Comment

>>> sum(b) over (order by rowid)

ordering by rowid  doesn't give meaningful sorting
0

LVL 3

Expert Comment

slightwv:
Author didnt mention anything about the root column. your query will work based on rownum and you have added WITH clause and UNION and all. I dont know you have added these things. Author may use UNION for example. That's why I post my query.

slightwv: and sdstuber:
Can you please explain "rowid  doesn't give meaningful sorting". in which case it will be failed.
0

LVL 76

Expert Comment

rowid provides no order on anything so why have it?  The question implies there is a necessary order for the sums to work correctly.

>>on rownum and you have added WITH clause and UNION and all.

I added the with and union only to provide sample data.  The main select is basically the same as what you posted.
0

LVL 73

Expert Comment

>>> Can you please explain "rowid  doesn't give meaningful sorting". in which case it will be failed.

what is there to explain?  In what case can it succeed?

rowid is simply an address of physical location.  It has no relation to the data content at all.

That's like trying to sort people by age by looking at the city of their birth.  The location has no meaning for sorting purposes
0

LVL 3

Expert Comment

if I want to fetch data in the same order in which i have inserted then rowid will be meaningful. Author didnt say anything about root column to find the calculated column. if author specify the calculated column should be based on the column then ROWID is meaningless. As per me, In this situation SORTING based on ROWID better than ROWNUM. So i have assumed calculated column should be calculated based on the order in which inserted.
0

LVL 73

Expert Comment

>>> if I want to fetch data in the same order in which i have inserted then rowid will be meaningful.

nope  - this is a common misconception.  I have no idea why people believe it to be true, but it's not.

From the Oracle SQL reference...

``````Rowids contain the following information:
¦ The data block of the data file containing the row. The length of this string
¦ The row in the data block.
¦ The database file containing the row. The first data file has the number 1. The
length of this string depends on your operating system.
¦ The data object number, which is an identification number assigned to every
database segment. You can retrieve the data object number from the data
dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. Objects
that share the same segment (clustered tables in the same cluster, for example)
have the same object number.
``````

That's all.  a rowid is simply physical location.  There is nothing at all in this that indicates order of insert.
Yes it's "possible" that 2 rows inserted into a new block will be sequential.  However there is no guarantee that they will be in the same block.
but even if they are,  there is nothing about rowid's that ensures the first row in the block was the earliest row.
0

Author Closing Comment

Many thanks "sdstuber" for leading me to the MODEL clause.  This engine is news to me.

As far as I can see Analytic functions like SUM OVER, scans the current dataset.  However, it sadly can't reference itself (ie a previous, calculated row)

True, my example could be solved by SUM OVER, but, what I perhaps didn't make very clear, was the importance of an "IF" statement in the "calculated" column than could reference the very same, "calculated" column.

eg if the previous running total value was over 5 for instance then restart the running total at 0.
0

LVL 73

Expert Comment

actually you still might be able to do that because the analytic functions have a windowing clause.

however,  for maximum "if" type logic and self-referencing  then model is the way to go.
model is complex,  I'm far from an expert in it, but when it's a good fit, there's little, if anything, better
0

## Featured Post

### Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…