Nested Cursor
-
I'm try to update a table using a stored proc. The table has a four field primary key. The table stores some gain information. The key is
effective_date loan_instrument_name
state_code loan_amount_lower_limit
I also have a field called expiration date. The expiration date is null until a new set of effective dates are entered.
Once the new effective date combo is entered I want to populate the previous expiration date with a date. Ex. I have a row '20031125','cf30','OH',30000, null
which is the current row for ohio.
User adds '20031225','cf30','OH',30000, null.
which is the new row. I want to poulate the expiration date of the previous row where eff_date is less than the new eff_date and the other pri keys are equal. I tried two cursors, one getting the max and the other comparing and updating as needed but It's not working. Any suggestions.
My Code
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'sp_ps_pop_gos_exp_date'
AND type = 'P')
DROP PROCEDURE sp_ps_pop_gos_exp_date
GO
CREATE PROCEDURE sp_ps_pop_gos_exp_date
AS
BEGIN
SET NOCOUNT ON
DECLARE @eff_date_max DATETIME;
DECLARE @ln_inst_max VARCHAR(30);
DECLARE @st_cde_max VARCHAR(30);
DECLARE @lwr_ln_lmt_max NUMERIC(10,2);
DECLARE @fetch INT ;
SELECT @eff_date_max = max(effective_date)FROM dbo.gain_on_sale;
DECLARE exp_date_max_cur CURSOR FOR
SELECT loan_instrument_name,
state_code,
loan_amount_lower_limit
FROM dbo.gain_on_sale g
WHERE @eff_date_max = effective_date
AND expiration_date IS NULL ;
OPEN exp_date_max_cur
FETCH NEXT FROM exp_date_max_cur
INTO @ln_inst_max,
@st_cde_max,
@lwr_ln_lmt_max
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @eff_date DATETIME;
DECLARE @ln_inst VARCHAR(30);
DECLARE @st_cde VARCHAR(30);
DECLARE @lwr_ln_lmt NUMERIC(10,2);
DECLARE @exp_date DATETIME;
DECLARE exp_date_cur CURSOR FOR
SELECT effective_date, loan_instrument_name, state_code, loan_amount_lower_limit,expiration_date
FROM dbo.gain_on_sale g
WHERE
AND expiration_date IS NULL ;
OPEN exp_date_cur
FETCH NEXT FROM exp_date_cur
INTO @eff_date,
@ln_inst,
@st_cde,
@lwr_ln_lmt,
@exp_date
WHILE @@FETCH_STATUS =0
BEGIN
IF
dbo.sf_ps_dateonly(@eff_date) <> dbo.sf_ps_dateonly(@eff_date_max)
AND @exp_date is null
BEGIN
UPDATE dbo.gain_on_sale
SET expiration_date = @eff_date
WHERE
loan_instrument_name = @ln_inst
AND state_code = @st_cde
AND loan_amount_lower_limit= @lwr_ln_lmt
END
FETCH NEXT FROM exp_date_cur
INTO @eff_date,
@ln_inst,
@st_cde,
@lwr_ln_lmt,
@exp_date
END
CLOSE exp_date_cur
DEALLOCATE exp_date_cur
FETCH NEXT FROM exp_date_max_cur
INTO @ln_inst_max,
@st_cde_max,
@lwr_ln_lmt_max
END
CLOSE exp_date_max_cur
DEALLOCATE exp_date_max_cur
--SET NOCOUNT OFF
END
-
I fixed the sql by using the variables from the first cursor to retrieve my rows in the second cursor. I'm only selecting the rows that match the pri keys and updating the appropiate records new code
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'sp_ps_pop_gos_exp_date'
AND type = 'P')
DROP PROCEDURE sp_ps_pop_gos_exp_date
GO
CREATE PROCEDURE sp_ps_pop_gos_exp_date
AS
BEGIN
SET NOCOUNT ON
DECLARE @eff_date_max DATETIME;
DECLARE @ln_inst_max VARCHAR(30);
DECLARE @st_cde_max VARCHAR(30);
DECLARE @lwr_ln_lmt_max NUMERIC(10,2);
DECLARE @fetch INT ;
SELECT @eff_date_max = max(effective_date)FROM dbo.gain_on_sale;
DECLARE exp_date_max_cur CURSOR FOR
SELECT loan_instrument_name,
state_code,
loan_amount_lower_limit
FROM dbo.gain_on_sale g
WHERE @eff_date_max = effective_date
AND expiration_date IS NULL ;
OPEN exp_date_max_cur
FETCH NEXT FROM exp_date_max_cur
INTO @ln_inst_max,
@st_cde_max,
@lwr_ln_lmt_max
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @eff_date DATETIME;
DECLARE @ln_inst VARCHAR(30);
DECLARE @st_cde VARCHAR(30);
DECLARE @lwr_ln_lmt NUMERIC(10,2);
DECLARE @exp_date DATETIME;
DECLARE exp_date_cur CURSOR FOR
SELECT effective_date, loan_instrument_name, state_code, loan_amount_lower_limit,expiration_date
FROM dbo.gain_on_sale g
WHERE
loan_instrument_name= @ln_inst_max
AND state_code= @st_cde_max
AND loan_amount_lower_limit=@lwr_ln_lmt_max
AND expiration_date IS NULL ;
OPEN exp_date_cur
FETCH NEXT FROM exp_date_cur
INTO @eff_date,
@ln_inst,
@st_cde,
@lwr_ln_lmt,
@exp_date
WHILE @@FETCH_STATUS =0
BEGIN
IF
dbo.sf_ps_dateonly(@eff_date) <> dbo.sf_ps_dateonly(@eff_date_max)
AND @exp_date is null
BEGIN
UPDATE dbo.gain_on_sale
SET expiration_date = CASE WHEN @eff_date <> @eff_date_max
THEN DATEADD(dd, -1, @eff_date_max)
ELSE @exp_date
END
WHERE
effective_date =@eff_date
AND loan_instrument_name = @ln_inst
AND state_code = @st_cde
AND loan_amount_lower_limit= @lwr_ln_lmt
END
FETCH NEXT FROM exp_date_cur
INTO @eff_date,
@ln_inst,
@st_cde,
@lwr_ln_lmt,
@exp_date
END
CLOSE exp_date_cur
DEALLOCATE exp_date_cur
FETCH NEXT FROM exp_date_max_cur
INTO @ln_inst_max,
@st_cde_max,
@lwr_ln_lmt_max
END
CLOSE exp_date_max_cur
DEALLOCATE exp_date_max_cur
SET NOCOUNT OFF
END
-
Can you use a 'JOINed" UPDATE instead:
UPDATE Older SET Older.expiration_date = Newer.effective_date
FROM dbo.gain_on_sale Older
JOIN dbo.gain_on_sale Newer
On Older.loan_instrument_name = Newer.loan_instrument_name
and Older.state_code = Newer.state_code
and Older.loan_amount_lower_limit = Newer.loan_amount_lower_limit
and Older.effective_date < Newer.effective_date
and Older.expiration_date Is NULL
and Newer.expiration_date Is NULL
Did not test for accuracy, just an example.
Once you understand the BITs, all the pieces come together
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply