mkrausnick
Programmer
I'm getting
However, I'm only referencing one table.
The purpose of the CTE is to identify and consolidate overlapping insurance enrollment periods. It is run from a stored procedure stored in a different database. 'ACE' is the code for the insurance program.
Here's the CTE:
Only one table is referenced, right? Any ideas as to why this doesn't work?
Thanks for your help.
Mike Krausnick
Dublin, California
Code:
Msg 4405, Level 16, State 1, Line 1
View or function 'overlap' is not updatable because the modification affects multiple base tables.
However, I'm only referencing one table.
The purpose of the CTE is to identify and consolidate overlapping insurance enrollment periods. It is run from a stored procedure stored in a different database. 'ACE' is the code for the insurance program.
Here's the CTE:
Code:
;with overlap as (
select Enrollment_ID as Ace1Enrollment_ID,pid as Ace1Pid,Ins_Eff_Dt as Ace1EffDate,Ins_End_Dt as Ace1EndDate,
ace.AceEnrollment_ID, ace.acepid,ace.aceEffDate,ace.aceEndDate,
case when Ace1.Ins_Eff_Dt < ace.aceEffDate then Ace1.Ins_Eff_Dt else ace.aceEffDate end as MinEffDate,
case when Ace1.Ins_End_Dt > ace.aceEndDate then Ace1.Ins_End_Dt else ace.aceEndDate end as MaxEndDate
from [Oneeapp].[dbo].oea_program_enrollment Ace1
inner join (
select Enrollment_ID as aceEnrollment_ID,pid as acePid,
Ins_Eff_Dt as aceEffDate,Ins_End_Dt as aceEndDate ,
CONVERT(datetime,'') as ModStartDate
from [Oneeapp].[dbo].oea_program_enrollment where Prog_Id = 'ACE') ace
on Ace1.pid = acepid and Ace1.Ins_Eff_Dt between aceEffDate and aceEndDate and Ace1.Ins_End_Dt > aceEndDate
where Ace1.Prog_Id = 'ACE' and Ace1.Enrollment_ID <> Ace.aceEnrollment_ID )
update overlap set Ace1EffDate=MinEffDate, Ace1EndDate=MaxEndDate,aceEffDate=MinEffDate,aceEndDate=MaxEndDate
Only one table is referenced, right? Any ideas as to why this doesn't work?
Thanks for your help.
Mike Krausnick
Dublin, California