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

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.
hydroITAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:

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)])

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
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
 
hydroITAuthor Commented:
Thanks, slightwv, for your interest:

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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
slightwv (䄆 Netminder) Commented:
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
 
hydroITAuthor Commented:
=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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
/

Open in new window

0
 
gajmpCommented:
select rn, a, b, sum(b) over (order by rowid) as calculated from t1
0
 
slightwv (䄆 Netminder) Commented:
gajmp,

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

Also, rowid isn't a great column to use for sorting.
0
 
sdstuberCommented:
>>> sum(b) over (order by rowid)

ordering by rowid  doesn't give meaningful sorting
0
 
gajmpCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
>>> 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
 
gajmpCommented:
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
 
sdstuberCommented:
>>> 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
depends on your operating system.
¦ 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.

Open in new window


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
 
hydroITAuthor Commented:
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
 
sdstuberCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.