How to query this?

[Arun Immanuel]

Hi everyone!. I have 2 columns in a table say col1 and col2.

Sample Data:

col1 col2
60 1000
20 0
30 0
40 1500
20 0
70 0
80 0

I need a query that will display the following output:
col1 col2 col3

60 1000 1000
20 0 980
30 0 950
40 1500 1500
20 0 1480
70 0 1410
80 0 1330

Description:

if col2 not equal to ZERO then
col3=col2.
else
col3=previous col3 - current col1

I don't need cursors.

Thanks in advance.

Regards,
Arun Kumar.A

[Colin Angus Mackay]

Arun.Immanuel wrote:

I don't need cursors.

You mean you don't "want" cursors. "Need" and "want" are different concepts. The only way I can see to do this without cursors is in the client side code. i.e. in the application that calls the SQL. Now, two importance concepts about databases here: 1. The data is essentially unordered. The implementation of the database may appear to give the data an order, but that is just incidental. You should not rely on that order. If you want guaranteed order then you have to define something in the table that you can ORDER BY 2. When retrieving data, an individual row has no concept of the rows around it - or even if there are any rows around it - so it will not be able to subtract anything from a column on the "previous" row. (There is no order, remember, so the concept of "previous" or "next" does not apply)

---

Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website

[Arun Immanuel]

Thank U very much. I have done that with the .NET code. I just want to know , if there exists any query. Regards, Arun Kumar.A

[Arun Immanuel]

Hi, Thank U very much for Ur reply. Sorry for not explaining the entire scenario. I know that the rows in SQL are not stored in order. But, this is what I am doing: I am querying the above 2 columns using "ORDER BY Date". Here "Date" is another column based on which I am retrieving the records. So, the output will definitely be in order. Thus we can refer the value of row(n) from row(n+1) since I am having the column Date with all the dates(i.e.no date missing). And the query would be: select A.col1,A.col2,B.col2 from tblName A, ( select Date-1 "Dte",col2 from tblName )B where A.Date=B.Dte Here B.col2 will be shifted 1 level with respect to A.col1. I have not included the order by clause here. So, I will be able to refer previous row from the current row. But, I do not know how to implement the required output as mentioned above. Any help would be appreciated. Regards, Arun Kumar.A -- modified at 6:41 Sunday 22nd April, 2007

[DQNOK]

Have you been successful yet? I'd like to see your solution if you have. I toyed with this for a couple of minutes, but quickly realized that your algorithm can best be stated in pseudo-code like this:

getCol3( rownum )
{
   if( 0 = col2(rownum) ) Then
      return getCol3(rownum-1) - col1(rownum);
   else
      return col2(rownum);
}

which is a *recursive* procedure. Some DBMs support recursive queries (Oracle and DB2 for instance), but I'm not sure even they would work here as they tend to recurse on *existing* field values, not on generated values like you want here. Check out the CONNECT BY clause if you're interested. I'm inclined to think Colin is correct here; I SERIOUSLY doubt this can be done without procedures. I have similar requirements in my tables, but have resigned myself to putting the table updates into loops within code. Let us know if you do figure out a clever way. Good luck. David