[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-04
20
Medium Priority
?
264 Views
Last Modified: 2014-08-11
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......
0
Comment
Question by:ernie_shah
  • 7
  • 6
  • 4
  • +2
20 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40239804
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.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40239976
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?
0
 
LVL 78

Expert Comment

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

 

Author Comment

by:ernie_shah
ID: 40241306
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.
0
 

Author Comment

by:ernie_shah
ID: 40241325
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.....
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40241466
"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.
0
 
LVL 74

Expert Comment

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

Author Comment

by:ernie_shah
ID: 40242056
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40242071
I'm not sure how your change helped explain.

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

Author Comment

by:ernie_shah
ID: 40242103
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
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40242191
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 40244163
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.
0
 

Author Comment

by:ernie_shah
ID: 40249011
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....
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40249768
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.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 200 total points
ID: 40249850
I think I got it.  It's not obvious since you're generating new rows to be included in the output as well as returning a rolling sum with resets at 100.

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

Open in new window


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)

Open in new window

0
 

Author Comment

by:ernie_shah
ID: 40253541
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
0
 
LVL 78

Expert Comment

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

Expert Comment

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

Open 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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40253745
as for version support,   yes,  MODEL was supported starting in 10gR1
0
 

Author Closing Comment

by:ernie_shah
ID: 40253999
Thanks guys for all your support, patience and prompt assistance.  The solution worked and I am very happy indeed.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

829 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