also, you'll attract more volunteers if you don't cut the points to the minimum.

I'm already here so no biggie -but for future questions or to get input from others you might want to bump it up.

Solved

Posted on 2014-08-04

I would like to compare one row to another, if the first row meets the criteria then row1 add row2 call this query1, if query1 meets the criteria then query1 add row3 etc.. for example

date bbls rate

2014-03-01 10 100

2014-03-02 20 100

2014-03-05 50 100

2014-03-05 30 100

using the example above: If bbls on 2014-03-01 < 100 then 2014-03-01 (bbls10) add 2014-03-02 (bbls20) query1(30)

if query1(30) < 100 then query1 add 2014-03-05 (bbls 50) query2

if query2 (80) < 100 then query2 add 2014-03-05 (bbls30) query3

if query3 > 100 then query3 - 100

I want to be able to perform this row by row calculation......

Can anyone help me please......

date bbls rate

2014-03-01 10 100

2014-03-02 20 100

2014-03-05 50 100

2014-03-05 30 100

using the example above: If bbls on 2014-03-01 < 100 then 2014-03-01 (bbls10) add 2014-03-02 (bbls20) query1(30)

if query1(30) < 100 then query1 add 2014-03-05 (bbls 50) query2

if query2 (80) < 100 then query2 add 2014-03-05 (bbls30) query3

if query3 > 100 then query3 - 100

I want to be able to perform this row by row calculation......

Can anyone help me please......

20 Comments

also, you'll attract more volunteers if you don't cut the points to the minimum.

I'm already here so no biggie -but for future questions or to get input from others you might want to bump it up.

This line in particular is very unclear:

"if query3 > 100 then query3 - 100"

I think you are trying to say: "if the result of query three (which added the values of a few fews) > 100, then subtract 100".

But, then put this result where, or do what with this result?

Also, is this a one-time task, or will this be an on-going task that will need to be repeated at some interval?

And, do you need the results to be saved back to the database somewhere? Or, do you only need the calculated results to be displayed in a report or screen?

While we wait for you to provide expected results (or a better explanation), all I got from the original question is you want to look ahead.

You can do this in Oracle but only for the NEXT rows values.

This is done with the LEAD function:

http://docs.oracle.com/cd/

The final results should be:

date bbls rate

2014-03-01 10 100

2014-03-02 20 100

2014-03-05 50 100

2014-03-05 30 100

2014-03-05 10 100

The last row where it is greater than 100 it does the calculation and returns the row with the same date as the row above.

Hope this is clearer.

But, then put this result where, or do what with this result *****This result is added to the original rows as in the example above

Also, is this a one-time task, or will this be an on-going task that will need to be repeated at some interval? **** This is an ongoing task that will need to be repeated monthly as the data changes.

And, do you need the results to be saved back to the database somewhere? Or, do you only need the calculated results to be displayed in a report or screen. *****I need the calculated results to be displayed on a screen in a report using Oracle Reports 10g

Hope this was clearer.....

Hope this is clearer."

No, that is not all clear.

You say: "The last row where it is greater than 100".

What is "it"? How is "it" calculated?

Then "it does the calculation"

Again, what is "it" here? And, exactly which calculation?

"returns the row with the same date as the row above."

So, you want just the date from the row above, or the whole row above, or you want most of the row above but with a calculated value that is different?

I am not all clear on what you are trying to do.

"I need the calculated results to be displayed ... in a report using Oracle Reports 10g.

Hope this was clearer..... "

Yes, that much is clear. You only need these results to be calculated and displayed. They don't need to be saved back to the database. Usually the best way to do a task like this is in a database view that does the calculations, then your Oracle Report query can simply select the columns from the view, So, the view does most of the work. The report simply displays the results. That is the most efficient way to get the job in Oracle.

But, exactly what the calculations need to be is not all clear to me.

date bbls rate rolling_total_

2014-03-01 10 100 10

2014-03-02 20 100 30

2014-03-05 50 100 80

2014-03-05 30 100 110 -- we hit 100, so wrap around to 10

2014-03-05 10 100 10

but I see a problem - How do you sort the two 2014-03-05 rows?

That is - How do we know the 50 comes before the 30?

In this particular example it wouldn't make a difference to the final sum, but it could cause intermediate results to look funny

Also, if your sum is exactly 100, do you want bbls to be 100, or 0?

And - is your 100 rollover fixed? Or, is it based on the rate column? And if so, does it roll on the current row's rate or previous row's rate? With all of them being 100, it's not possible to tell

date bbls rate

2014-03-01 10 100

2014-03-02 20 100

2014-03-06 50 100

2014-03-05 30 100

2014-03-05 10 100

Also, if your sum is exactly 100, do you want bbls to be 100, or 0? *******i want the bbls to remain the same in this case

And - is your 100 rollover fixed? *******Yes it is fixed

date bbls rate

2014-03-01 10 100

2014-03-02 20 100

2014-03-04 50 100

2014-03-05 30 100

2014-03-05 10 100

Is that something like this?

date bbls rate running_total

2014-03-01 10 100 10

2014-03-02 20 100 30

2014-03-04 50 100 80

2014-03-05 30 100 10

2014-03-05 10 100 20

date bbls rate

2014-03-01 10 100

2014-03-02 20 100

2014-03-04 50 100

2014-03-05 30 100

2014-03-06 20 100

2014-03-07 50 100

2014-03-08 40 100

If so, would the desired results be like

2014-03-01 10 100

2014-03-02 20 100

2014-03-04 50 100

2014-03-05 30 100

2014-03-05 10 100

2014-03-06 20 100

2014-03-07 50 100

2014-03-08 40 100

2014-03-08 20 100

where the 10 values is carried over for the next calculation?

Or would the last line look like

2014-03-08 10 100

where the calculation only starts over with the 2014-03-06 row?

What is the datatype of the date field (true date or character datatype)?

A table description with more sample data and the expected results would be most helpful.

Are they possibly grouped in some fashion (e.g. by an id or a division)? **** No the data is not grouped

You are absolutely correct the the data would look like:

Also, can the data look like this?

date bbls rate

2014-03-01 10 100

2014-03-02 20 100

2014-03-04 50 100

2014-03-05 30 100

2014-03-06 20 100

2014-03-07 50 100

2014-03-08 40 100

If so, would the desired results be like

2014-03-01 10 100

2014-03-02 20 100

2014-03-04 50 100

2014-03-05 30 100

2014-03-05 10 100

2014-03-06 20 100

2014-03-07 50 100

2014-03-08 40 100

2014-03-08 10 100

where the calculation only starts over with the 2014-03-06 row? Yes the calculation would restart with this row

What is the datatype of the date field (true date or character datatype)? The datatype is DATE

Hope this is clearer....

But, given your data above, this query does what you're looking for...

The MODEL clause is supported in 10gR1 and above

```
SELECT yourdate, CASE WHEN n = 1 THEN bbls ELSE special END bbls, rate
FROM (SELECT *
FROM yourtable
MODEL
DIMENSION BY(ROW_NUMBER() OVER (ORDER BY yourdate) rn)
MEASURES(yourdate, bbls, rate, bbls running_total, TO_NUMBER(NULL) special)
RULES
(running_total [rn > 1] =
CASE
WHEN (running_total[CV() - 2] + bbls[CV() - 1]) > 100
THEN
bbls[CV()]
WHEN (running_total[CV() - 1] + bbls[CV()]) > 100
THEN
(running_total[CV() - 1] + bbls[CV()]) - 100
ELSE
(running_total[CV() - 1] + bbls[CV()])
END,
special [rn > 1] =
CASE
WHEN (running_total[CV() - 1] + bbls[CV()]) > 100
THEN
(running_total[CV() - 1] + bbls[CV()]) - 100
ELSE
NULL
END)),
(SELECT 1 n FROM DUAL
UNION ALL
SELECT 2 FROM DUAL)
WHERE n = 1 OR (n = 2 AND special IS NOT NULL)
ORDER BY yourdate, n
```

if anybody wants to test it on their own system, you can use this to create the sample data

```
WITH yourtable
AS (SELECT DATE '2014-03-01' yourdate, 10 bbls, 100 rate FROM DUAL
UNION ALL
SELECT DATE '2014-03-02', 20, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-04', 50, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-05', 30, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-06', 20, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-07', 50, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-08', 40, 100 FROM DUAL)
```

when I try creating the table above i am getting the following error:

WITH incentives3 as

(SELECT DATE '2014-03-01' date1, 10 bbls, 100 rate FROM DUAL

UNION ALL

SELECT DATE '2014-03-02', 20, 100 FROM DUAL

UNION ALL

SELECT DATE '2014-03-04', 50, 100 FROM DUAL

UNION ALL

SELECT DATE '2014-03-05', 30, 100 FROM DUAL

UNION ALL

SELECT DATE '2014-03-06', 20, 100 FROM DUAL

UNION ALL

SELECT DATE '2014-03-07', 50, 100 FROM DUAL

UNION ALL

SELECT DATE '2014-03-08', 40, 100 FROM DUAL)

*

Error at line 15

ORA-00928: missing SELECT keyword

That said: That was just to dummy up some test data. You should try your query against your real table.

```
WITH incentives3
AS (SELECT DATE '2014-03-01' date1, 10 bbls, 100 rate FROM DUAL
UNION ALL
SELECT DATE '2014-03-02', 20, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-04', 50, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-05', 30, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-06', 20, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-07', 50, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-08', 40, 100 FROM DUAL)
SELECT date1, CASE WHEN n = 1 THEN bbls ELSE special END bbls, rate
FROM (SELECT *
FROM incentives3
MODEL
DIMENSION BY(ROW_NUMBER() OVER (ORDER BY date1) rn)
MEASURES(date1, bbls, rate, bbls running_total, TO_NUMBER(NULL) special)
RULES
(running_total [rn > 1] =
CASE
WHEN (running_total[CV() - 2] + bbls[CV() - 1]) > 100
THEN
bbls[CV()]
WHEN (running_total[CV() - 1] + bbls[CV()]) > 100
THEN
(running_total[CV() - 1] + bbls[CV()]) - 100
ELSE
(running_total[CV() - 1] + bbls[CV()])
END,
special [rn > 1] =
CASE
WHEN (running_total[CV() - 1] + bbls[CV()]) > 100
THEN
(running_total[CV() - 1] + bbls[CV()]) - 100
ELSE
NULL
END)),
(SELECT 1 n FROM DUAL
UNION ALL
SELECT 2 FROM DUAL)
WHERE n = 1 OR (n = 2 AND special IS NOT NULL)
ORDER BY date1, n
```

However, as noted above, you shouldn't need to do this. The WITH was posted for everyone else.

You already have the real table. So you should use that instead

Title | # Comments | Views | Activity |
---|---|---|---|

Oracle Licensing on vCPU - Vmware | 7 | 76 | |

Delphi selector screen | 2 | 45 | |

Update table using multiple table qualifications | 12 | 30 | |

Calculating total percentage per row in Oracle Query | 2 | 23 |

This video shows how to recover a database from a user managed backup

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

Connect with top rated Experts

**20** Experts available now in Live!