I want to compare one row to another then perform action in oracle database

what do you expect the final results to be?

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.

I also don't understand what you are trying to do, or what is supposed to happen after you add the values from two, or three or four rows, and arrive at a total higher than the value you are checking for.  

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?

I also do not understand what you are wanting.

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/E11882_01/server.112/e41084/functions086.htm#SQLRF00656

Thanks for the response.

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.

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".    ****This is correct

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.....

"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."

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.

I think you're looking for this...

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

ok, i see what you mean (good point i will have to get back to the user on that one)....lets say the result set looked like this below:

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

I'm not sure how your change helped explain.

Why is 2014-03-06 before 2014-03-05 in the results?

Why is 2014-03-06 before 2014-03-05 in the results?  ****** my error it should be:

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

OK, so that is your sample data.  Now what exactly do you want to see on the report?

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

Also, are these calculations made on a periodic basis (e.g. for each month) or continuous? Are they possibly grouped in some fashion (e.g. by an id or a division)? 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     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 these calculations made on a periodic basis (e.g. for each month)  ***** yes these calculations are made on a periodic basis for each month

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....

If you want just those three columns in your output, then no, it is not clear at all to me what is being calculated or reset.  Or, at least it is not clear why the calculation is being done if that doesn't get displayed or indicated anywhere.

Thank you sdstuber for your solution.... but i am using oracle 10g will this still work....

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

by default sqplus doesn't like blank lines in a statement.  Remove the blank line and try again.

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

If you are trying to use the WITH clause by itself, that's the problem.  You use the WITH clause as part of the SELECT statement

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

Select allOpen in new window

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

as for version support,   yes,  MODEL was supported starting in 10gR1

Thanks guys for all your support, patience and prompt assistance.  The solution worked and I am very happy indeed.