@David Green -- thank you for your response, but I'm afraid you're working at a whole other level than me...I'm not sure what is meant by saving off @ID @RECORD and @DICT
As
@Shawn Waldie correctly inferred, I'm an Ellucian Colleague customer, which means there are a few layers of abstraction between where I develop my code and what actually gets run. The Colleague Studio tooling actual prevents me from putting in statements that they haven't designed for their cross-database runtime. It "generates" what I write into some code that winds up in $APPHOME/cc/com/datatel/server/cc/CC.G82.ADDRESS.SANITY.CHECK
I can edit that generated code, and compile it with
BASIC CC.SRC.DIR TO CC.OBJ.DIR CC.G82.ADDRESS.SANITY.CHECK
(Thanks Jonathan for the reminder about CD, my tools hide that from me and I was baffled that I couldn't get CRT output)
But as soon as a change is made with Colleague Studio my changes will get stomped. For debugging/investigating directly editing the generated code is fine, but I expect blow back from my management if I try to circumvent vendor tooling in production :)
@Ian McGowan the generated subroutine does a fair bit of wrangling to preserve savelist 0. I added some instrumentation CRTs to the PRESERVE.LIST.ZERO and RESTORE.LIST.ZERO internal subroutines. I now believe the savelist preservation is working reliably, but confidence isn't super high.
@Jonathan Smith Thank you for the insights on indexes, and your continued investigation. No indexes on this computed column. But that does explain an issue I had with an index becoming stale in another circumstance a month or so ago. I must have missed the caution about adding indexes on computed columns to other entities.
So
my code looks like this:
A.VALID = 'Y'
*In Colleague land, no country means USA or Canada which is represented with a CN
IF NOT(A.COUNTRY = 'CN') AND NOT(A.COUNTRY = 'USA') AND NOT(A.COUNTRY = '') THEN
*We only have data for Canada and the US at this point
RETURN
END
IF NOT(A.ZIP) THEN
A.VALID = 'N'
RETURN
END
X.ZIP = A.ZIP
CONVERT " " TO "" IN X.ZIP
CONVERT "-" TO "" IN X.ZIP
V.G82AV.PROV.STATE = ""
IF NUM(X.ZIP) THEN
*US zip code
X.FIRST.FIVE = X.ZIP[1,5]
X.CRIT = "WITH G82AV.RANGE.MATCH.START <= ":X.FIRST.FIVE:" AND G82AV.RANGE.MATCH.END >= ":X.FIRST.FIVE
IF LEN(X.FIRST.FIVE) = 5 THEN
FOR_THE SELECTED G82.ADDRESS.VALIDATION.ID USING NEWLIST CRITERIA X.CRIT
END_THE G82.ADDRESS.VALIDATION.ID
END
END
ELSE
*Canadian postal code
X.FIRST.CHAR = X.ZIP[1, 1]
X.CRIT = "WITH G82AV.PREFIX.MATCH = '":X.FIRST.CHAR:"'"
FOR_THE SELECTED G82.ADDRESS.VALIDATION.ID USING NEWLIST CRITERIA X.CRIT
END_THE G82.ADDRESS.VALIDATION.ID
END
IF NOT(A.STATE) THEN
A.VALID = 'N'
RETURN
END
IF V.G82AV.PROV.STATE AND V.G82AV.PROV.STATE = A.STATE THEN
A.VALID = 'Y'
END
ELSE
A.VALID = 'N'
END
Which is in turn
generated into this. I've run it through a formatter, and added some CRT instrumentation to the savelist manipulation.
SUBROUTINE CC.G82.ADDRESS.SANITY.CHECK(A.VALID, A.COUNTRY, A.STATE, A.ZIP)
EXEC_MERGELIST = SELECTINFO(0,1)
IF EXEC_MERGELIST THEN
GOSUB PRESERVE.LIST.ZERO
END
GOSUB USER.MAIN.CODE
IF EXEC_MERGELIST THEN
GOSUB RESTORE.LIST.ZERO
END
RETURN
* ================================================================ *
USER.MAIN.CODE:
OPEN "", "G82.ADDRESS.VALIDATION" TO F.G82.ADDRESS.VALIDATION ELSE NULL
A.VALID = "Y"
IF ((NOT((A.COUNTRY EQ "CN")) AND NOT((A.COUNTRY EQ "USA"))) AND NOT((A.COUNTRY EQ ""))) THEN
RETURN
END
IF NOT(A.ZIP) THEN
A.VALID = "N"
RETURN
END
X.ZIP = A.ZIP
X.ZIP = CONVERT(" ", "", X.ZIP)
X.ZIP = CONVERT("-", "", X.ZIP)
V.G82AV.PROV.STATE = ""
IF NUM(X.ZIP) THEN
X.FIRST.FIVE = X.ZIP[1,5]
X.CRIT = (("WITH G82AV.RANGE.MATCH.START <= " : X.FIRST.FIVE) : " AND G82AV.RANGE.MATCH.END >= ") : X.FIRST.FIVE
IF (LEN(X.FIRST.FIVE) EQ 5) THEN
CRTRIA = TRIMF(X.CRIT)
IF ((CRTRIA[1,5] NE "WITH ") AND (CRTRIA[1,7] NE "SAMPLE ")) THEN
CRTRIA = "WITH " : CRTRIA
END
AVAIL_LIST = 0
FOR AVAIL_LIST_COUNTER = 9 TO 1 STEP -1 UNTIL AVAIL_LIST
IF SELECTINFO(AVAIL_LIST_COUNTER, 1) EQ 0 THEN
AVAIL_LIST = AVAIL_LIST_COUNTER
END
NEXT AVAIL_LIST_COUNTER
FORMLIST '' TO AVAIL_LIST
HUSH ON SETTING HUSH.SETTING
EXECUTE 'SELECT G82.ADDRESS.VALIDATION ' : CRTRIA: ' TO ' : AVAIL_LIST
HUSH HUSH.SETTING
CRT "Range select used list ":AVAIL_LIST
READLIST LIST_23 FROM AVAIL_LIST ELSE LIST_23 = ""
G82.ADDRESS.VALIDATION.ID.COUNT = DCOUNT(LIST_23, CHAR(254))
G82.ADDRESS.VALIDATION.IDS.PROCESSED = 0
ABORT.G82.ADDRESS.VALIDATION.ID.LOOP = 0
LOOP
REMOVE V.G82.ADDRESS.VALIDATION.ID FROM LIST_23 SETTING LIST_23_DELIMITER
UNTIL (NOT(V.G82.ADDRESS.VALIDATION.ID) AND NOT(LIST_23_DELIMITER)) OR (ABORT.G82.ADDRESS.VALIDATION.ID.LOOP) DO
G82.ADDRESS.VALIDATION.IDS.PROCESSED = G82.ADDRESS.VALIDATION.IDS.PROCESSED + 1
OPEN "", "G82.ADDRESS.VALIDATION" TO F.G82.ADDRESS.VALIDATION ELSE NULL
KV.G82.ADDRESS.VALIDATION = V.G82.ADDRESS.VALIDATION.ID
READ R.G82.ADDRESS.VALIDATION FROM F.G82.ADDRESS.VALIDATION, KV.G82.ADDRESS.VALIDATION THEN
G82.ADDRESS.VALIDATION.ADD.MODE = 0
END ELSE
G82.ADDRESS.VALIDATION.ADD.MODE = 1
R.G82.ADDRESS.VALIDATION = ''
END
V.G82AV.RANGE.MATCH.END = R.G82.ADDRESS.VALIDATION<5>
V.G82AV.RANGE.MATCH.START = R.G82.ADDRESS.VALIDATION<3>
V.G82AV.COUNTRY = R.G82.ADDRESS.VALIDATION<1>
V.G82AV.PREFIX.MATCH = R.G82.ADDRESS.VALIDATION<4>
V.G82AV.PROV.STATE = R.G82.ADDRESS.VALIDATION<2>
REPEAT
END
END ELSE
X.FIRST.CHAR = X.ZIP[1,1]
X.CRIT = ("WITH G82AV.PREFIX.MATCH = '" : X.FIRST.CHAR) : "'"
CRTRIA = TRIMF(X.CRIT)
IF ((CRTRIA[1,5] NE "WITH ") AND (CRTRIA[1,7] NE "SAMPLE ")) THEN
CRTRIA = "WITH " : CRTRIA
END
AVAIL_LIST = 0
FOR AVAIL_LIST_COUNTER = 9 TO 1 STEP -1 UNTIL AVAIL_LIST
IF SELECTINFO(AVAIL_LIST_COUNTER, 1) EQ 0 THEN
AVAIL_LIST = AVAIL_LIST_COUNTER
END
NEXT AVAIL_LIST_COUNTER
FORMLIST '' TO AVAIL_LIST
HUSH ON SETTING HUSH.SETTING
EXECUTE 'SELECT G82.ADDRESS.VALIDATION ' : CRTRIA: ' TO ' : AVAIL_LIST
HUSH HUSH.SETTING
CRT "Prefix select used list ":AVAIL_LIST
READLIST LIST_31 FROM AVAIL_LIST ELSE LIST_31 = ""
G82.ADDRESS.VALIDATION.ID.COUNT = DCOUNT(LIST_31, CHAR(254))
G82.ADDRESS.VALIDATION.IDS.PROCESSED = 0
ABORT.G82.ADDRESS.VALIDATION.ID.LOOP = 0
LOOP
REMOVE V.G82.ADDRESS.VALIDATION.ID FROM LIST_31 SETTING LIST_31_DELIMITER
UNTIL (NOT(V.G82.ADDRESS.VALIDATION.ID) AND NOT(LIST_31_DELIMITER)) OR (ABORT.G82.ADDRESS.VALIDATION.ID.LOOP) DO
G82.ADDRESS.VALIDATION.IDS.PROCESSED = G82.ADDRESS.VALIDATION.IDS.PROCESSED + 1
OPEN "", "G82.ADDRESS.VALIDATION" TO F.G82.ADDRESS.VALIDATION ELSE NULL
KV.G82.ADDRESS.VALIDATION = V.G82.ADDRESS.VALIDATION.ID
READ R.G82.ADDRESS.VALIDATION FROM F.G82.ADDRESS.VALIDATION, KV.G82.ADDRESS.VALIDATION THEN
G82.ADDRESS.VALIDATION.ADD.MODE = 0
END ELSE
G82.ADDRESS.VALIDATION.ADD.MODE = 1
R.G82.ADDRESS.VALIDATION = ''
END
V.G82AV.RANGE.MATCH.END = R.G82.ADDRESS.VALIDATION<5>
V.G82AV.RANGE.MATCH.START = R.G82.ADDRESS.VALIDATION<3>
V.G82AV.COUNTRY = R.G82.ADDRESS.VALIDATION<1>
V.G82AV.PREFIX.MATCH = R.G82.ADDRESS.VALIDATION<4>
V.G82AV.PROV.STATE = R.G82.ADDRESS.VALIDATION<2>
REPEAT
END
IF NOT(A.STATE) THEN
A.VALID = "N"
RETURN
END
IF (V.G82AV.PROV.STATE AND (V.G82AV.PROV.STATE EQ A.STATE)) THEN
CRT "CC returing Y"
A.VALID = "Y"
END ELSE
CRT "CC returning N"
A.VALID = "N"
END
RETURN
* ================================================================ *
PRESERVE.LIST.ZERO:
AVAILABLE_SELECT_LISTS = ''
TWO_AVAIL_LIST_FOUND = ''
FOR AVAIL_LIST_COUNTER = 9 TO 1 STEP -1 UNTIL TWO_AVAIL_LIST_FOUND
IF SELECTINFO(AVAIL_LIST_COUNTER, 1) EQ 0 THEN
AVAILABLE_SELECT_LISTS<-1> = AVAIL_LIST_COUNTER
IF LEN(AVAILABLE_SELECT_LISTS) = 2 THEN
TWO_AVAIL_LIST_FOUND = 1
END
END
NEXT AVAIL_LIST_COUNTER
AVAIL_LIST_1 = AVAILABLE_SELECT_LISTS<1>
AVAIL_LIST_2 = AVAILABLE_SELECT_LISTS<2>
HUSH ON SETTING HUSH.SETTING
CLEARSELECT AVAIL_LIST_1
CLEARSELECT AVAIL_LIST_2
EXECUTE 'MERGE.LIST 0 UNION ':AVAIL_LIST_1:' TO ':AVAIL_LIST_2
CLEARSELECT 0
CLEARSELECT AVAIL_LIST_1
HUSH HUSH.SETTING
CRT "PRESERVE.LIST.ZERO:":'MERGE.LIST 0 UNION ':AVAIL_LIST_1:' TO ':AVAIL_LIST_2
RETURN
* ================================================================ *
RESTORE.LIST.ZERO:
HUSH ON SETTING HUSH.SETTING
EXECUTE 'MERGE.LIST ':AVAIL_LIST_1:' UNION ':AVAIL_LIST_2:' TO 0'
CLEARSELECT AVAIL_LIST_1
CLEARSELECT AVAIL_LIST_2
HUSH HUSH.SETTING
CRT "RESTORE.LIST.ZERO:":'MERGE.LIST 0 UNION ':AVAIL_LIST_1:' TO 0'
RETURN
END
So, thank you Jonathan for the pointer about CD -- that's not a manual step I have to do when working in Studio. As I can now generate some CRT output
AWARREN@r18dev_lin:GET.LIST CNADDR 1000 records retrieved to list 0.
AWARREN@r18dev_lin>SELECT ADDRESS SAMPLE 5
5 records selected to list 0.
AWARREN@r18dev_lin>SELECT ADDRESS WITH ADDR.G82.SANITY.CHECK = 'Y' PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
CC returning N
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
Prefix select used list 9
CC returing Y
1 records selected to list 0.
Naturally we'd want to see this with a LIST too
AWARREN@r18dev_lin:GET.LIST CNADDR 1000 records retrieved to list 0.
AWARREN@r18dev_lin>SELECT ADDRESS SAMPLE 5
5 records selected to list 0.
AWARREN@r18dev_lin>LIST ADDRESS COUNTRY STATE ZIP ADDR.G82.SANITY.CHECK WITH ADDR.G82.SANITY.CHECK = 'Y' PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
LIST ADDRESS COUNTRY STATE ZIP ADDR.G82.SANITY.CHECK WITH ADDR.G82.SANITY.CHECK = 'Y' 21:40:22 Jan 06 2022 1
ADDRESS... COUNTRY............. ST E........ Sane?
169119 CN ON L0N1S7 Y
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
1758236 CN ON L5C 1G5 Y
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
1758237 CN ON L5C 1G5 Y
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
CC returning N
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
Prefix select used list 9
CC returing Y
169122 CN ON M5J2N4 Y
4 records listed
So, it looks to be avoiding stepping on list 0, and using MERGE.LIST to avoid saving the list out to a file to preserve it. It's also interesting that when this runs it's flagging the results properly -- indicating that it's returning a Y...Or returing a Y...sigh. Not gonna fix that typo before posting.
I'm having a hard time escaping the notion that SELECT and LIST are processing criteria differently. On a whim I just tried using SSELECT, and it appears to yield the correct results
AWARREN@r18dev_lin:GET.LIST CNADDR 1000 records retrieved to list 0.
AWARREN@r18dev_lin>SELECT ADDRESS SAMPLE 5
5 records selected to list 0.
AWARREN@r18dev_lin>SSELECT ADDRESS WITH ADDR.G82.SANITY.CHECK = 'Y' PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
Prefix select used list 9
CC returing Y
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
PRESERVE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 7
CC returning N
RESTORE.LIST.ZERO:MERGE.LIST 0 UNION 9 TO 0
Prefix select used list 9
CC returing Y
4 records selected to list 0.
If you've made it this far down, I should buy you a beer if we're ever able to meet up. Thank you all for your consideration and feedback!