Rocket U2 | UniVerse & UniData

 View Only

 Criteria Apply Differently in LIST vs SELECT

Alan Warren's profile image
Alan Warren posted 01-05-2022 10:16
Hi Folks,

First time posting here, but I've been working with UniData for about 5 years now after mostly working with MySQL and Oracle databases.    We get UniData through a VAR, but I'm pretty sure they think I'm crazy so hoping to get some wisdom from the community here.

I've got a computed column ADDR.G82.SANITY.CHECK defined as
SUBR("CC.G82.ADDRESS.SANITY.CHECK",COUNTRY,STATE,ZIP)

The subroutine opens another entity and selects against it to try to match postal code first letter to province and state to ZIP to see if addresses look "sane".

If I use the computed column in SELECT criteria with an active list, it fails with wildly wrong results.  However, if I execute a
SELECT ADDRESS WITH COUNTRY = 'CN' SAMPLE 1000
SAVE.LIST CNADDR
GET.LIST CNADDR
LIST ADDRESS COUNTRY STATE ZIP WITH ADDR.G82.SANITY.CHECK = 'Y'
<<lists 922 correct records>>

Trying it with SELECT
GET.LIST CNADDR
SELECT ADDRESS WITH ADDR.G82.SANITY.CHECK = 'Y'
<<returns two records, not 922>>

The generated code does do some list protection, but I'm wondering if SELECT possibly does some parallel processing that make list protection logic prone to failure when executed in computed columns?

So, I guess I'm hoping someone can shed some light on the internals of select vs list criteria application.  Or if there are best practices for applying select criteria in a CC subroutine?

Manu Fernandes's profile image
PARTNER Manu Fernandes
Hi

In uniquery/retrieve you can't nest SELECT in subr of I-type. 
Your subselect return error, Check the capturing. 

When I need it, I build a index key and use BASIC SELECTINDEX inside the subr of the itype, it works very good. 

Do you need a sample ? 
Alan Warren's profile image
Alan Warren
Thanks for the response @Manu Fernandes!  I seem to have picked the wrong format for this question, as I can't reply to you and have to post my reply as an "answer".

Constantly learning :)

I'm not sure what you mean by "check the capturing"​...like executing the select with a "-C"?  Unfortunately I've got a vendor layer between my code and the generated I-type subroutine. 

The SELECTINDEX idea sounds brilliant, but our VAR's tooling prohibits me from using SELECTINDEX or their own S.GET.INDEX.

I can't for the life of me find a document saying "thou shalt not select from a computed column", such a document would be great to send to my VAR as their tooling actually facilitates it.

I guess I'll have to consider re-working my design to allow key based access to my validation file, or writing this bit of functionality outside of my vendor's supported tooling
Manu Fernandes's profile image
PARTNER Manu Fernandes
Re

Has I understand, into CC.G82.ADDRESS.SANITY.CHECK there is EXECUTE 'SELECT....'

You can put a CAPTURING to retrieve the error. 

More over, into UVHOME directory there is a 'errrlog' file which log all execution error. You can read yours into. 

If the uv/errlog file did not existe create it. (touch)  with everybody write right. Then rerrun your sample. 
All errors appears into errlog file. 

Regards
Alan Warren's profile image
Alan Warren
Thanks again Manu,

We're on UniData but I was able to ​enable error logging by creating 
/usr/ud82/include/msglevelconfig

I wasn't seeing any error output in there, so I added a syntax error to the criteria, re-compiled and sure enough getting lots of great info in $UDTBIN/udt.errlog.

So, applying the select in the I-SUBR isn't generating an error.  Perhaps UniData is more permissive on this than Universe?

I tried adding a capturing, but I can't seem to make anything appear on the output on the console or the udt.errlog.
David Green's profile image
David Green
Alan,

In your subroutine try to save off @ID, @RECORD, and @DICT.  Then restore them before exiting.​​
Jonathan Smith's profile image
ROCKETEER Jonathan Smith
Alan,

Firstly does the ADDRESS file have an index on the ADDR.G82.SANITY.CHECK field. If you do a LIST.INDEX ADDRESS at ECL it will tell you which fields are indexed.
A SELECT command will use an Index if present, if the index is 'stale' then this would account for the behaviour you are seeing, as a SELECT will use the value in the index not the value (or calculated value) from the file. A LIST command will display the value (or calculated value) on the file at the time the LIST command is issued.

We do issue warnings about indexing on caculated or derived values,  a simple example is having a file with a customer number in it and having a dictionary using a TRANSLATE function to the 'CUSTOMERS' file to get the customer name. You can create an index on your file indexing against the name from the CUSTOMERS file. However if the CUSTOMERS file is upated with a new name then the index on your file is now incorrect and has become stale. The reason being the CUSTOMERS file knows nothing about the index on your file. Another example would be to create an index on someone's age from their date of birth, this will become stale very quickly.

This could be the same situation that you are seeing if the ADDR.G82.SANITY.CHECK field is indexed and relies on calculated or derived information.

There is a tool called guide_ndx which can be used to check not just the physical condition of an index in UniData but also the logical condition of the file and report mismatches between what is in the file and what is indexed.

So if ADDR.G82.SANITY.CHECK is indexed then the command 'guide_ndx -x3,ADDR.G82.SANITY.CHECK ADDRESS' will check the index file for both logical and physical errors. The output files from the command will be called GUIDE_XERROR.LIS and GUIDE_XSTATS.LIS

If GUIDE_XERROR.LIS contains any errors then the index will be need to rebuilt, the quickest way is to delete the index, recreate it and build again. This in itself comes with some extra warnings if you need to do this.

As a first step, check to see if the field is indexed, and if it is indexed run guide_ndx at level 3 to report any errors and we can then discuss next steps.
Ian McGowan's profile image
Ian McGowan
If the subroutine CC.G82.ADDRESS.SANITY.CHECK is doing a SELECT statement internally, it's probably overwriting the default select list 0.  The two records you get are from whatever the last ADDRESS record selects.  If you can change the sub, doing something like EXECUTE "SELECT BLAH FROM ADDRESS..." RTNLIST SANITY.CHECK and then where you use that (perhaps with another execute, add PASSLIST SANITY.CHECK, or if it's a readnext do READNEXT ID FROM SANITY.CHECK), that might help.

I think Manu is saying the same thing, but I'm not into that whole brevity thing or whatever man :-)
Shawn Waldie's profile image
Shawn Waldie
Alan, I'm a colleague developer.
There are a few ways you can protect your incoming savedlist, and if you send a snippet of your code, I can offer a suggestion or two.
Jonathan Smith's profile image
ROCKETEER Jonathan Smith
Looking at Alan's description more carefully it's unlikely to be an index problem and will be a problem with using mutilple select lists (the advice I gave on index checking and virtual attributes etc is still good troubleshooting advice).

If we could see the code involved we would know how to avoid the problem you are seeing as it appears the SELECT statement using the WITH clause is destroying select list 0 in the subroutine where the LIST statement using the same WITH condition is not destroying select list 0.

If you could provide the code for "CC.G82.ADDRESS.SANITY.CHECK" we should be able to work out what is going on and where the protection needs to go in the code.
Jonathan Smith's profile image
ROCKETEER Jonathan Smith
I beleive the problem that Alan is seeing can be demostrated using the UniData demo account

I wrote a simple program called ADD.CHECK

SUBROUTINE ADD.CHECK(RET.VALUE,STATE,ZIP)
EXECUTE 'SELECT STATES WITH @ID = ' : DQUOTE(STATE) CAPTURING SCREEN
IF SYSTEM(11) = 0 THEN RET.VALUE = 0 ELSE RET.VALUE = 1
END

I compiled and cataloged the program
I then added a dictionary into the CUSTOMER file called ADD.SAN.CHECK

001: I
002: SUBR("ADD.CHECK",STATE,ZIP)
003:
004: Add Check
005: 1R
006: S

I then did a CD CUSTOMER before using the new dictionary

At ECL if you do LIST CUSTOMER WITH ADD.SAN.CHECK = 1 STATE ZIP you will see the results you expect (15 in my case)

LIST CUSTOMER WITH ADD.SAN.CHECK = 1 STATE ZIP 21:40:03 06 Jan 2022 1
Cust...... ST Zip Code

12 WI 98733
3 NY 10017
201 CO 80209
204 CO 80209-44
44
207 CO 80209
210 WI 53140
190 CO 80301
202 CO 80403
4 WI 53140
25 WI 53142
208 KS 50505
206 CO 80209
11 TX 60606
8 WI 50208
2 CO 80443
15 records listed

If you however do SELECT CUSTOMER WITH ADD.SAN.CHECK = 1 you get the following

1 records selected to list 0.

>

So there is a difference in the way UniData itself is dealing the two commands.

The problem is also further complicated if you do the following

SELECT CUSTOMER WITH STATE
SAVE.LIST JDS
GET.LIST JDS
LIST CUSTOMER STATE ZIP ADD.SAN.CHECK
LIST CUSTOMER STATE ZIP ADD.SAN.CHECK 21:51:53 06 Jan 2022 1
Cust...... ST Zip Code Add Check

12 WI 98733 0
1 record listed

So in summary the problem is complicated by the data and program itself, without seeing the whole program and the files being used it will be very difficult to advise on a process that will allow this to work everytime. We can look at looking other select lists other than 0 or even using a SELECTINDEX but we need to see everything that is going on.
Alan Warren's profile image
Alan Warren
@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!
Jonathan Smith's profile image
ROCKETEER Jonathan Smith
@Alan Warren @Ian McGowan @Shawn Waldie

I changed my code to use preservation and restoration of select list 0 and did some more internal testing. The problem will only occur if you do SELECT followed by SELECT or GET.LIST followed by SELECT. I did a quick review of the process route being followed by UniData when you do the different combinations and the SELECT, SELECT or GET.LIST, SELECT does follow a different route so is being processed differenlty internally in the UniData engine itself. I'll continue looking to see if there is anything we can do to work round but for now just avoid the combination of doing one SELECT after another or a GET.LIST then a SELECT at ECL. A Single SELECT will work fine.

For reference this is what I did in my demo account.
I copied the record 'CO' in my STATES file to '+CO+' and deleted the 'CO' record.
I changed the ADD.CHECK program to now be

$BASICTYPE "U" ; *** Force UniData Parsing of Commands
SUBROUTINE ADD.CHECK(RET.VALUE,STATE,ZIP)
DEBUG.ON = 1
SELZERO.ACTIVE = 0
RET.VALUE = 0
IF SELECTINFO(0,1) = 1 THEN
*** Select List 0 - Active - Preserve List 
  SELZERO.ACTIVE = 1
  CLEARSELECT 9
  CLEARSELECT 8
  EXECUTE 'MERGE.LIST 0 UNION 9 TO 8' CAPTURING OUTPUT
  IF DEBUG.ON THEN
    CRT "Select List 0 Active - Saved and Cleared"
    CRT OUTPUT
  END
  CLEARSELECT 0
END
CLEARSELECT 7
EXECUTE 'SELECT STATES WITH @ID = ' : DQUOTE(STATE) : ' TO 7' CAPTURING OUTPUT
IF SELECTINFO(7,1) = 1 THEN RET.VALUE = 1
CLEARSELECT 7
IF SELZERO.ACTIVE THEN
*** Restore List 0
  EXECUTE 'MERGE.LIST 9 UNION 8 TO 0' CAPTURING OUTPUT
  CLEARSELECT 9
  CLEARSELECT 8
  IF DEBUG.ON THEN
    CRT "Select List 0 Restored"
    CRT OUTPUT
  END  
END
IF DEBUG.ON THEN
  CRT "RET.VALUE set to " : RET.VALUE
END
END​

​With this

LIST CUSTOMER STATE ADD.SAN.CHECK works​​​​​​​​
LIST CUSTOMER WITH ADD.SAN.CHECK = 1 STATE works
SELECT CUSTOMER WITH ADD.SAN.CHECK = 1 works

What does not work is

SELECT CUSTOMER WITH STATE
SAVE.LIST JDS
GET.LIST JDS
SELECT CUSTOMER WITH ADD.SAN.CHECK = 1

This also fails
SELECT CUSTOMER WITH STATE
SELECT CUSTOMER WITH ADD.SAN.CHECK = 1

This works
SELECT CUSTOMER WITH STATE AND WITH ADD.SAN.CHECK = 1

This works
SELECT CUSTOMER WITH STATE
LIST CUSTOMER WITH ADD.SAN.CHECK = 1

So it is a double SELECT or GET.LIST,SELECT that is causing the problem, I will continue to look at this later today to see if I can workout a workaround but for now avoid the double SELECT or GET.LIST,SELECT combination and try and do the SELECT in one go.
Jonathan Smith's profile image
ROCKETEER Jonathan Smith
@Alan Warren @Ian McGowan @Shawn Waldie

I have a workaround which eliminates all the need for select list presevation as it avoids the EXECUTE 'SELECT ....' in the subroutine.
There is less code and should run quicker as it's more efficent, it does require an understanding of SETINDEX though, if you need some advice on SETINDEX I am happy to provide it.

So in my example the first thing I did was a create an index on the ID of the STATES file (Yes I know it sounds crazy ... but this is one of the legimate uses of doing it)
CREATE.INDEX STATES @ID
BUILD.INDEX STATES ALL

I wrote a new program called ADD.CHECK.1

$BASICTYPE "U" ; *** Force UniData Parsing of Commands
SUBROUTINE ADD.CHECK.1(RET.VALUE,STATE,ZIP)
*** Alternative Method Using SETINDEX and UniBasic to avoid the use of SELECT in ADD.CHECK
*** This should allow a SELECT,SELECT or GET.LIST,SELECT to work without problem
RET.VALUE = 0
COMMON /STATES/ STATES.INIT , F.STATES
IF NOT(STATES.INIT) THEN
  OPEN "STATES" TO F.STATES THEN STATES.INIT = 1 ELSE RETURN
END
*** Using Named Common this way ensures file is opened once in a session no matter
*** how many times the subroutine is called
SETINDEX '@ID',STATE ON F.STATES
READFWD R.STATES FROM F.STATES THEN
*** R.STATES contains the Record Read from the STATES file
  K.INDEXNODE = FILEINFO(F.STATES,21)
*** FILEINFO(filevar,21) will give you the last index key used by READFWD
  IF K.INDEXNODE = STATE THEN RET.VALUE = 1
END
RETURN
END​

​I created a new dictionary called ADD.SAN.CHECK.1 in DICT CUSTOMER and did a CD CUSTOMER

:AE DICT CUSTOMER ADD.SAN.CHECK.1
Top of "ADD.SAN.CHECK.1" in "DICT CUSTOMER", 9 lines, 117 characters.
*--: P
001: I
002: SUBR("ADD.CHECK.1",STATE,ZIP)
003:
004: Add Check
005: 1R
006: S

:SELECT CUSTOMER WITH ADD.SAN.CHECK.1 EQ "1"

16 records selected to list 0.
>CLEARSELECT

:GET.LIST JDS
26 records retrieved to list 0.
>SELECT CUSTOMER WITH ADD.SAN.CHECK.1 EQ "1"

16 records selected to list 0.

>CLEARSELECT
​​​​
I tested all the other scenarios using this method and they all work.
Also no need for SELECT list preservation.

In your case you would need to create indexes on and their maybe a bit more checking you need to do in UniBasic.
I hope this helps everyone understand what can be done to work round the situation.
Shawn Waldie's profile image
Shawn Waldie
I think I understand what you're attempting to accomplish.

If so, this is how I would go about it:

BEGIN CASE
   CASE NOT(A.ZIP) OR NOT(A.STATE)
      A.VALID = 'N' 
      PROCESS.END = 1
   CASE NOT(A.COUNTRY = 'CN') AND NOT(A.COUNTRY = 'USA') AND NOT(A.COUNTRY = '')
      A.VALID = 'Y'
      PROCESS.END = 1
   CASE 1
      A.VALID = 'N'
      PROCESS.END = 0

END CASE

IF NOT(PROCESS.END) THEN
   X.ZIP = A.ZIP
   CONVERT " " TO "" IN X.ZIP
   CONVERT "-" TO "" IN X.ZIP
   *V.G82AV.PROV.STATE = "" *I don't think you want this

   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
            IF V.G82AV.PROV.STATE AND V.G82AV.PROV.STATE = A.STATE THEN
* there shouldn't be any reason to continue searching the G82.ADDRESS.VALIDATION file if you find at least one record where this condition is met, right?
               A.VALID = 'Y'
               ABORT.G82.ADDRESS.VALIDATION.LOOP = 1
            END
         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
         IF V.G82AV.PROV.STATE AND V.G82AV.PROV.STATE = A.STATE THEN
* see note above...same logic here I think
            A.VALID = 'Y'
            ABORT.G82.ADDRESS.VALIDATION.LOOP = 1
         END
      END_THE G82.ADDRESS.VALIDATION.ID
   END
END

Note that unless you have GOSUBs in your code (which we don't in this case), Colleague Studio will provide the final 'RETURN', so I left that off.
Note also that I'm taking advantage of envision variables PROCESS.END and ABORT.G82.ADDRESS.VALIDATION.LOOP
Jonathan Smith's profile image
ROCKETEER Jonathan Smith
@Shawn Waldie If you can change the code so that it does not require a EXECUTE 'SELECT ....' which can be done using SETINDEX it will

Work for all the selection cases @Alan Warren identified
It will remove the requirements for the select list preservation and restoratrion
It will run faster and be more efficent (espically if named common is used to open the file only once, not every time the subroutine is called)

​​
Shawn Waldie's profile image
Shawn Waldie
@Jonathan Smith i have no doubt you're correct.  Unfortunately, if Alan wants to use this i-descriptor at the Colleague application layer, it must be developed within Colleague Studio, which doesn't permit the initialization of named COMMONs (same goes for SETINDEX). Although he could set up indexing on the G82.ADDRESS.VALIDATION file (Colleague provides the necessary utilities for it), taking advantage of any performance gains.
Alan Warren's profile image
Alan Warren
Hey @Jonathan Smith @Shawn Waldie

Thank you both for continuing to look into this.​  I have a few tools that are written outside of the Colleague environment to help me with administrative work, so it's always beneficial for me to learn more techniques for improving efficiency.

My earliest training on UniData and the colon prompt encouraged the use of incrementally built lists (multiple sequential selects) as a way of directing the database to whittle things down as quickly as possible.  Back in my Oracle days I made use of EXPLAIN ​and occasional comment hints (they probably had a more formal name I can't recall).  Is there any sort of tool like that in UniData?  

So, given that this works as expected in LIST, and even in SSELECT can we agree that the difference in behaviour for the criteria handling should be classified as a bug in UniData?  (we're on 8.2 build 9115)

In the meantime I can work around this by remembering to either include nested select computed columns in the primary select statement, or ensuring that SSELECT is used for such computed columns.

​Shawn, I've always tried to avoid touching the variables introduced by the generator; but the point about using the ABORT.G82.ADDRESS.VALIDATION.LOOP is well taken.  There've been times I wanted to use continue/exit logic in a FOR_<text>​, so I definitely need to tuck this away.  In this circumstance the query is only expected to return 0 or 1 matches so no effort was made to optimize the loop.  I would love to be able to use COMMON in IS Subroutines, but understand that it's not feasible right now.

The assigning to the buffer of fields (Vdot) was to silence warnings about uninitialized variables.  The procedure is defined as reference only, so I figured it was safe to do even if it does look a little silly.  I bet the wizards on this forum have a means of silencing those uninitialized variable warnings -- for the most part I take it as valid feedback and go initialize.

Thank you so much!
Jonathan Smith's profile image
ROCKETEER Jonathan Smith
@Alan Warren @Shawn Waldie We don't have an EXPLAIN keyword or command in UniData (UniVerse does) ... however EXPLAIN or INFORM just provide you with insight into how the command parser is going to execute a command in terms of order and use of indexes. In terms of this case it would not offer you any more insight into what is going on.

In terms of silencing the 'uninitialized' variables message, there is HUSHBASIC ON but all that does is hide the program name the error occured in. The correct method would be to fix the program generating the error.​ Another side effect of the error is that it also gets logged to the udt.errlog file, so if it occurs a lot it will be putting a lot of error messages into the log. An EXECUTE CAPTURING would of cause hide it ... but the work required to do that and get the programs to work in the same way it would be far easier to fix the program itself generating the error.

​I strongly suspect that UniData has behaved this way for a long time with the combination of multiple selects and using a virtual field that itself performs a select. At face value it would appear to be a bug, however given the ease of the work around (do everything in one select statement) and the risks involved with changing the UniData code in such a cruical area I wouldn't expect that we wi​ll be making any changes soon.

Thanks,
Jonathan Smith
UniData ATS