cursor without cursors 
Author Message
 cursor without cursors
Hi

I have table with field "activation_date", which is unique. I sort it on
this field. And I want update field "previous_act" with date from field
"activation_date" from the next record. Of course I can do it using cursor
(but its very expansive). Can I do it other way?

Or other trouble in the same problem:

there is to fields:

value, date.

I sort it over date and i need 3rd column with sum from first row to current
(without cursor)

Marek

Marek



Tue, 26 Oct 2004 18:38:12 GMT
 cursor without cursors

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are.  Also, tables have rows, not records; columns, not
records.  Tables have no "first", "last" or "next" row -- that is a
sequential file.  You are still thinking in terms of cursors and
sequential processing.

CREATE TABLE Foobar
(activation_date DATETIME NOT NULL PRIMARY KEY,
 previous_act DATETIME NOT NULL,
 CHECK (previous_act <= activation_date),
 running_total INTEGER NOT NULL,
 value INTEGER NOT NULL DEFAULT 0,
 ..);

You can use scalar subquery expressions to build each new row, like
this.

INSERT INTO Foobar (activation_date, previous_act, running_total, ...)
VALUES ('2002-01-01',
       (SELECT MAX(activation_date) FROM Foobar),
       (SELECT SUM(value) FROM Foobar),
        5,
        ...);

This is Standard SQL-92, but you might have change it depending on
your release of SQL Server.



Tue, 26 Oct 2004 23:04:05 GMT
 cursor without cursors

Quote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in
> your schema are.  Also, tables have rows, not records; columns, not
> records.  Tables have no "first", "last" or "next" row -- that is a
> sequential file.  You are still thinking in terms of cursors and
> sequential processing.

OK.

Quote:
> CREATE TABLE Foobar
> (activation_date DATETIME NOT NULL PRIMARY KEY,
>  previous_act DATETIME NOT NULL,
>  CHECK (previous_act <= activation_date),
>  running_total INTEGER NOT NULL,
>  value INTEGER NOT NULL DEFAULT 0,
>  ..);

> You can use scalar subquery expressions to build each new row, like
> this.

> INSERT INTO Foobar (activation_date, previous_act, running_total, ...)
> VALUES ('2002-01-01',
>        (SELECT MAX(activation_date) FROM Foobar),
>        (SELECT SUM(value) FROM Foobar),
>         5,
>         ...);

> This is Standard SQL-92, but you might have change it depending on
> your release of SQL Server.

It looks good - I need to think about it

THX

Marek



Wed, 27 Oct 2004 00:24:33 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. cursor without cursors

2. Cursor Cursor Cursor

3. Could not complete cursor operation, because the table schema changed after cursor was declared

4. Could not complete cursor operation, because the table schema changed after cursor was declared

5. Need Help Please , Invalid Cursor Position Error and Cursor Not Open Error

6. declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable

7. client side cursor vs. server side cursor

8. Why declare cursor and open cursor both cost same amount of time

9. Cursor in Trigger - with cursor not open when running second time in the same SQL session

10. Passing a CURSOR value to another cursor

11. setting dynamically created cursor to a variable cursor !!


 
Powered by phpBB® Forum Software