I've recently discovered a flaw in one of my nightly stored procedures which causes it to crash. This procedure was written to give Patron constituencies based on existing associations. The problem is that it will sometimes try to give a constituency of that particular type to someone who already had that constituency except that it's now inactive. The procedure is therefore crashing on a violation of PRIMARY KEY constraint, as it tries to add a new record of that customer_no and constituency (which together make the primary key for TX_CONST_CUST).
Bottom line, and possibly dumb question, I'm wondering if there's really any reason to keep inactive records in a TX table, since it seems that a lot of them don't do that (e.g. TX_CUST_KEYWORD). And, if so, it would seem that I could either delete all inactive records (e.g. where end_dt <= getdate()) of that constituency before I add them, or update the start_dt and end_dt for preexisting records accordingly.
Ideas?
Thanks, Tessiturians.
Hi Matt,
The rationale for keeping those inactive records is to have some historical knowledge of the constituency (i.e. they were a board member until 2005). That said, you can safely delete them without causing any problems. You just lose the history.
The other option is to expand the logic in your job a bit and check for an inactive constituency and then removing the end date to reactivate it. It will really depend on whether the start and end dates matter to you for that particular constituency.
- Levi
Unknown said: I've recently discovered a flaw in one of my nightly stored procedures which causes it to crash. This procedure was written to give Patron constituencies based on existing associations. The problem is that it will sometimes try to give a constituency of that particular type to someone who already had that constituency except that it's now inactive. The procedure is therefore crashing on a violation of PRIMARY KEY constraint, as it tries to add a new record of that customer_no and constituency (which together make the primary key for TX_CONST_CUST).
Use AP_MAINTAIN_CONSTITUENCY to add or reactivate the constituency. From the comments:
"Maintains constituencies in the TX_CONST_CUST table.
I (insert) - new row will be added to TX_CONST_CUST if not present.
if row is present, and inactive, the start data will be updated to today.
if row is present and active, no action will be taken."
Hello,
I think there are some inactive constituencies you could remove and there are others you might want to keep. It depends on how they are being used in your organization.
My group has a constituency for Board members and this is one that I would not delete even if it was inactive because although a customer is not currently on the board they once were and that is useful information. It is not information that is figured into reports or list making, but that screen is read by development officers and ticket managers when they open an account and it will change the interaction with the customer.
Jared
If you are updating the start date anyways (or don't care about the old start dates), I don't see any reason why you wouldn't just delete those records and add the new ones. I think this is a pretty common practice. Having accidentally deleted all constituencies before, I'd just say to be sure to test that thoroughly first. Thankfully for me I was in my Test system at the time.
Cheers!
Hi, Matt:
This is one I’ve played with a lot!
Here’s a pattern that we follow. In the example below, a constituency of “YA” (member of our Young Artist Program) gets added to accounts that have an active affiliation to the Young Artist Program account, but only if they don’t already have the constituency. That’s the first insert statement below. The second insert adds a “former Young Artist” constituency to those who have an inactive affiliation—but only if they don’t already have that constituency. The delete statement at the top gets rid of any constituencies that have no end date and that no longer deserve the constituency. So if you want to use constituency to mark length of service, put an end date in it. Or, if you prefer, delete the end date in the constituencies for patrons who have the associations you are looking for, then add constituencies to those patrons who don’t already have them.
Lucie
--------------------------------
DELETE TX_CONST_CUST
WHERE constituency in (74) --Young Artist Studio-Current
AND ISNULL(end_dt, 0) = 0
AND customer_no IN
(SELECT individual_customer_no
FROM T_AFFILIATION
WHERE group_customer_no = 299962 --FGO Young Artist Studio
AND affiliation_type_id = 10007 --Employee
AND inactive = 'Y')
INSERT TX_CONST_CUST(customer_no, constituency)
SELECT distinct individual_customer_no, 74 --Young Artist Studio-Current
AND inactive = 'N'
AND individual_customer_no NOT IN
(SELECT customer_no
FROM TX_CONST_CUST
WHERE constituency = 74)
AND ISNULL(individual_customer_no,0)<>0
INSERT tx_const_cust(customer_no, constituency)
SELECT distinct individual_customer_no, 75 --Young Artist Studio Alumnus/a
WHERE group_customer_no = 299962 Employee
AND inactive = 'Y'
WHERE constituency = 75)
______________________________Lucie SpielerIT Development and Training ManagerFLORIDA GRAND opera