Ok here is the deal.
There is this intranet website.

I basically have to create an automatic process of sending out the Renewal Notices when the customer’s subscription is about to expire.


There is a live table A with Customer Account information. There is another live Table B with customer shipping, ID, etc.  I had to gather a list of customers whose subscription ends in a certain date range and change their NoticeStatus to '1stNotice'...etc

 

I had to create a table C with all CustomerAccountInfo (from live table A) where subscription ends in 2 months from now. After that I update their renewal status to ‘1^st Notice’ .

 

Then on the main Live table A , I append all these records I just generated from table C, also doing case when ….statements.

Now, all the fields in the intranet gets uploaded automatically, besides one field Subscriber.

This subscriber field is apparently generated from unique non-clustered index in live Table B. I cannot change the structure of that table.

Table B holds CustomerPersonalInformation including a column cmp_code is the company code which needs to be put in the field Subscriber.

 

So after I insert into Table A, I tried updating Table B, using :

update cicmpy –this is the table B in my description

set cmp_code = (

select

enduser.cmp_code as Subscriber

 

from

RenewalLetter r –this is the table C in my description

left outer join cicmpy Subscriber on freeguidfield_01 = Subscriber.cmp_wwn

left outer join cicmpy Enduser on CustomerID = Enduser.cmp_wwn

 

where

type = 75

and ltrim(freetextfield_10) = 'Active'

and freedatefield_01 between

dateadd(mm,2,getdate())

AND dateadd(mm,2,getdate())+7

 

and freeboolfield_05 not like '1'

and subscriber.classificationid in ( 'HMO', 'EUR'))

 

And I get this error I explained in the previous post.