[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-17
17
Medium Priority
?
860 Views
Last Modified: 2012-05-12
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
Comment
Question by:hydroIT
  • 5
  • 5
  • 3
  • +2
17 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36983464
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

by:hydroIT
ID: 36983506
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36983518
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:hydroIT
ID: 36983633
=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

by:Shaju Kumbalath
ID: 36985190
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36985543
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 36985632

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
 
LVL 3

Expert Comment

by:gajmp
ID: 36986258
select rn, a, b, sum(b) over (order by rowid) as calculated from t1
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36986272
gajmp,

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

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

Expert Comment

by:sdstuber
ID: 36986273
>>> sum(b) over (order by rowid)

ordering by rowid  doesn't give meaningful sorting
0
 
LVL 3

Expert Comment

by:gajmp
ID: 36990191
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36990209
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 74

Expert Comment

by:sdstuber
ID: 36990243
>>> 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

by:gajmp
ID: 36990297
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 74

Expert Comment

by:sdstuber
ID: 36990318
>>> 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
 

Author Closing Comment

by:hydroIT
ID: 36990498
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 74

Expert Comment

by:sdstuber
ID: 36990582
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month20 days, 8 hours left to enroll

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question