Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB, but I am restricted to read-only.
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.
Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB , but I am restricted to read-only.
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the each latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.|||
Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far. I am working in SQL server 2005.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB , but I am restricted to read-only.
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.|||
You are returning the results of a data manipulation - there may not be a 'match' between the resultset and the actual table.
My 'simple' recommendation is to:
capture the resultset in a @.Table variable, then use the [ID] value from that table variable to UPDATE the data, finally, returning the contents of the table variable with a SELECT.|||Thanks for the reply. I posted this question a few too many times (was told it was deleted by administrator!).
Anyway, [ID] is not the primary key, [acmdtn_RECID] is. Any changes made to the master database simply add another record, and retain the old record, so historical queries can be run. So there can be dozens of records for each [ID]. What I want is the most recent record, for each [ID], as of a certain date.
I guess I just don't get why I can display the information, but I can't write back to the database based on that displayed information. It's a complex query, but there's no joins, or other tables involved, there can only be a 1:1 relationship between records in the table, and records in the resultset.
