By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Getting previous values of a field

P: n/a
Hi,

In oracle I have a LAG function using which I could get the previous
value of a field.
Do we have anything similar to that in SQL Server or access?
Thanks

Devi

Mar 15 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
devi (de******@gmail.com) writes:
In oracle I have a LAG function using which I could get the previous
value of a field.
Do we have anything similar to that in SQL Server or access?


I have no idea what LAG does in Oracle, but the only place you can
retrieve this information easily is when you are in an UPDATE or
DELETE trigger, in which case you find the information in the
virtual table "deleted". One the transaction is committed, the only
way to retrieve the information is through a log-reader tool.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 15 '06 #2

P: n/a

Sorry. I guess I wasnt clear in explaning my doubt...

I am creating a simple bug tracker application (in Access db) and i
created a hisotry table to
log the bug history.

The history table contains details like ProblemRecordNo (PRN),
RecordStatus, Assignee, Reporter (and also some more columns). The PK
for this table is ID. It contains multiple entries for a Record.

I have inserted a lot of records int History table and now I introduced
two new fields into History table. They are PreviousStatus and
PreviousAssignee. I wanted to update the previous values of the Status
and Assignee for each record.

Say my History table contains values like

ID PRN Status Assignee
1 10 Report UserA
2 10 InProcess UserA
3 10 Esclated UserB

Now after introducing the Previous fields, the History table should
look like

ID PRN Status Assignee PreviousStatus PreviousAssignee
1 10 Report UserA
2 10 InProcess UserA Report UserA
3 10 Esclated UserB InProcess UserA
In the first record the PreviousStatus and PreviousAssignee are empty
bse there is no previous values for those two items. the next two
records contain the previous values of the status and assignee.

I used the following sql statement to update the table, but I get a
script time out error

UPDATE (
SELECT H.PRN, H.ID, H.PREVIOUSSTATUS AS OLDSTATUS, N.STATUS AS
NEWSTATUS,
H.PREVIOUSASSIGNEE AS OLDASSIGNEE, N.ASSIGNEE AS NEWASSIGNEE FROM
HISTORY H,
(SELECT N.ID, N.PRN, N.STATUS, N.ASSIGNEE FROM HISTORY N ) N
WHERE H.PRN = N.PRN AND N.ID < H.ID AND N.ID IN
(SELECT MAX(M.ID) FROM HISTORY M WHERE M.ID < H.ID AND H.PRN = M.PRN) )
SET OLDSTATUS = NEWSTATUS,
OLDASSIGNEE = NEWASSIGNEE

Is there anyother way i could update the table with a optimized query
that doesnt take time to update large set of rows.

:(

Mar 16 '06 #3

P: n/a
devi (de******@gmail.com) writes:
I am creating a simple bug tracker application (in Access db) and i
created a hisotry table to
log the bug history.


In such case you should ask in comp.databases.ms-access. While both
SQL Server and Access are produced by the same Redmon company, there
are vast differences between the SQL dialects.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 16 '06 #4

P: n/a
Can you access LAG in PL/SQL? I thought is was a datamining function. It is
also available in SQL Server Analysis services.

--
Anith
Mar 16 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.