Rocket U2 | UniVerse & UniData

 View Only

 Need an Idescriptor function that can search for multiple substrings and return a boolean list

Shawn Waldie's profile image
Shawn Waldie posted 09-10-2024 11:19

Here is a record sample:

The TESTING column is a multi-valued list, and the pipes represent actual SVMs for readability.

I'm looking for a function that will return an associated boolean list indicating whether or not either '1400' or '9101' exists in TESTING, so...

In this sample, if one is present so is the other, but that's not always the case. So a 1 should be returned if either exists in what is actually a subvalued list.

EDIT 1: A subroutine would make this very easy, I know, but I don't have time for the administrative overhead that comes with developing/delivering one to PROD, so I need this to be "idescriptor" language.

Also, there will be subsequent processing, based on a SUM() of the Y/N list for each record, which will be compared to DCOUNT(TESTING,@VM).

EDIT 2: I think I found a solution. I didn't realize I could use the INDEX function on lists. Though it will require another step or 2 to get the final result, I think it will work.

Manu Fernandes's profile image
PARTNER Manu Fernandes posted 09-10-2024 12:21

hi

SUBROUTINE TEST(RESULT, VALS)
* RESULT MV'ED 1/0 if sv contains VALS comma separated
CONVERT ',' TO @AM IN VALS 
TESTINGFIELD = 1 ;* fixme 
TESTING = @RECORD<TESTINGFIELD>
NBMV = DCOUNT( TESTING,@VM) ;* define the qty of mv value 
NBV = DCOUNT(VALS,#AM) ;* define qty of VALS 
RESULT = '' 
FOR I = 1 TO NBMV ;* loop on all mv 
    RES = ''
    FOR II = 1 TO NBV ;* loop all vals on this mv 
        LOCATE(VALS<II>, TESTING, 1, NBMV ; RG ) THEN 
            RES += 1 ;* ok this is found 
        END ELSE 
            EXIT ;* get out the current loop, at leat one value is not found 
        END 
    NEXT II 
    RESULT<NBMV> = (RES = NBV)  ;* if all values are found ... set result to 1 else 0 
NEXT NBMV 
RETURN 
*---------------------------------

for the itype, defined it MV'ed and on the same ASSOCNAME than TESTING FIELD 

call method 

SUBR("TEST", "1400,1901") 
if you want set 1400,1901 as part of the query like LIST FILE WHEN TEST = "1400,1901" 
 
you get the statement from @SENTENCE 
retrieve the condition on TEST field into VALS 
you set the RESULT as a string (concat all VALS comma separate) "1400,1901" instead 1/0  
 
I hope this help
manu
Manu Fernandes's profile image
PARTNER Manu Fernandes posted 09-10-2024 12:29

the version where testing VALS are on the statement,

SUBROUTINE TEST(RESULT)

COMMON VALS 
IF UNASSIGNED(VALS) THEN 
    RES = TRIM(@SENTENCE)
    I = INDEX('WHEN TEST')
    VALS = FIELD(RES[I,999],' ',4) 
    CONVERT '"' TO '' IN VALS 
    CONVERT "'" TO '' IN VALS 
    CONVERT ',' TO @AM IN VALS 
END 

TESTINGFIELD = 1 
TESTING = @RECORD<TESTINGFIELD>

NBMV = DCOUNT( TESTING,@VM)
NBV = DCOUNT(VALS,#AM) 
RESULT = '' 
FOR I = 1 TO NBMV ;* LOOP ALL MV
    RES = ''
    FOR II = 1 TO NBV ;* LOOP ALL VALS 
        LOCATE(VALS<II>, TESTING, 1, NBMV ; RG ) THEN 
            RES<-1> = VALS<II>
        END ELSE 
            EXIT ;* OUT OF CURRENT FORLOOP THIS VALUE IS NOT FOUND ... 
        END 
    NEX TII 
    RESULT<NBMV> = CONVERT(@AM,',',RES)
NEXT NBMV 
RETURN 
*---------------------------------

ITYPE defined as MV on the same ASSOCNAME than TESTING FIELD 
ITYPE TESTING
SUBR("TEST") 

*-----------
LIST FILE WHEN TEST = "1400,1901"
Tyrel Marak's profile image
PARTNER Tyrel Marak posted 09-11-2024 09:56

Shawn, try this: two I-Types (I'll just include the 2nd line of each)

1. TESTING_FLAT
   CATS(CONVERT(@SM,'|',TESTING),REUSE('|'))

2. TESTING_MATCH
   ORS(NES(INDEXS(TESTING_FLAT,'1400|',1),REUSE('0')),NES(INDEXS(TESTING_FLAT,'9101|',1),REUSE('0')))

I think that gives you what you want.
Tyrel

Shawn Waldie's profile image
Shawn Waldie posted 09-11-2024 10:29

Thank you, @Tyrel Marak.  That is precisely what I ended up doing yesterday:

EVAL "

<<F(SAVEDLISTS,BEG.DT000,1)>>;

<<F(SAVEDLISTS,END.DT000,1)>>;

SUBR('S.GET.SCS.ID', '', @ID, @1, @2, 'I':@VM:'E', 'N', 'Y');

SUBR('S.TRANS','STUDENT.ACAD.CRED',@3,'STC.SCS.COURSE.SECTION','X');

SUBR('S.TRANS','COURSE.SECTIONS',@4,'SEC.COURSE.LEVELS','X');

CONVERT(@SM,'|', @5);

SUBR('-INDEXS',@6,'1400',1);

SUBR('-INDEXS',@6,'9101',1);

SUBR('-GTS',@7,REUSE(0));

SUBR('-GTS',@8,REUSE(0));

SUM(SUBR('-ORS',@9,@10));

DCOUNT(@3,@VM);

IF @11 EQ 0 THEN 'NO DEU' ELSE (IF @11 EQ @12 THEN 'ALL DEU' ELSE 'SOME DEU')

"

And I'll mention something else that had me stumped for about half-hour. That is, I initially had the EVAL expression wrapped in single quotes. This was fine until I added that last statement and the system returned a syntax error message. Stepping back and taking a broader view, I noticed that the system wrapped @1 and @2 in single quotes, so I'm surprised the error didn't appear earlier. In any case, another take away for me was wrap your EVAL expression in double quotes.