Multivalue number from LOWER(@RECORD)

Greg,

This is how I did it

1. Create a Basic Program

SUBROUTINE CHG.MEMBER.REC(RET_DATA,ENTIRE_RECORD)
ATT.CNT = DCOUNT(ENTIRE_RECORD,@AM)
NEW.RECORD = ""
FOR ATT.NXT = 1 TO ATT.CNT
   NEW.RECORD<1,ATT.NXT> = ATT.NXT : " - " : ENTIRE_RECORD<ATT.NXT>
NEXT ATT.NXT
RET_DATA = NEW.RECORD
RETURN
END

2. Compile and Catalog (global if you want available everywhere)

3. LIST.ITEM MEMBERS.NEW 09:00:47am  15 May 2025  PAGE    1

    0312
001 Markowski
002 Karl
003 B
004 Mr
005 8953 Rowes Drive
006 Williston
007 DC

    0621
001 Postek
002 Scott
003 P
004 Mr
005 13322 West Winter Place
006 Grand Haven
007 NY

3. LIST MEMBERS.NEW EVAL "SUBR("CHG.MEMBER.REC",@RECORD)" 09:01:15am  15 May 2025
PAGE    1
               SUBR("CHG.MEMBER.REC
MEMBERS.NEW    ",@RECORD)..........

0312           1 - Markowski
               2 - Karl
               3 - B
               4 - Mr
               5 - 8953 Rowes Drive
               6 - Williston
               7 - DC
0621           1 - Postek
               2 - Scott
               3 - P
               4 - Mr
               5 - 13322 West Winte
               r Place
               6 - Grand Haven
               7 - NY
0930           1 - Sun
               2 - Mike
               3 - U

Hi Greg,

Create a control record. As an example, assume that in the file called CONTROL there's a record called GUIDE which consists of the attribute number on each line, like this:
1
2
3
.
.
.

however many lines you need ... even 1000 or 10000.  It's easy to make this using excel, and then copy and paste the column into a record.

In an I-Type or with EVAL do something like this:

SPLICE(TRANS(CONTROL,'GUIDE',-1,'X')[@VM,1,DCOUNT(@RECORD,@AM)],' ',LOWER(@RECORD))

That should work without a subroutine.

Hope you find that useful.

------------------------------
Tyrel Marak
Technical Support Manager
Aptron Corporation
Florham Park NJ US
------------------------------

Thanks for your suggestion. A sub would open up the possibilities. 

However, I can see now I have not described my requirement very well. 

I need the attribute number and value in separate columns rather than concatenated.

Is is possible for a sub to return something akin to associated multivalues?

If not, I can always split the what you have provided using T-SQL after it passes through the odbc linked server, but it would obviously be better if it could all be done in UniVerse.

I also would like to return a translation from the corresponding attribute number in another identically structured record. I'm wondering if this could be incorporated into a third column processed in the sub, assuming of course that such an associated structure is possible at all.

That is very interesting. Kind of equivalent to using a tally table in T-SQL. I will have to play with it because I don't really understand it, having never used SPLICE(). 

I'm guessing it concatenates similarly to the solution provided by Jonathan.

If so, can your solution be expanded to return the attribute and values in different columns? 

Greg,

A small change to what I suggested will allow this as two I-Types (or EVALs):

FIELDS(SPLICE(TRANS(CONTROL,'GUIDE',-1,'X')[@VM,1,DCOUNT(@RECORD,@AM)],'|',LOWER(@RECORD)),'|',1,1)

FIELDS(SPLICE(TRANS(CONTROL,'GUIDE',-1,'X')[@VM,1,DCOUNT(@RECORD,@AM)],'|',LOWER(@RECORD)),'|',2,1)

@Greg Clitheroe

If you change the basic subroutine to be this and use two evals it work

SUBROUTINE CHG.MEMBER.REC.AGAIN(RET_DATA,ENTIRE_RECORD,PARSE_COLUMN)
ATT.CNT = DCOUNT(ENTIRE_RECORD,@AM)
RET_DATA = ""
FOR ATT.NXT = 1 TO ATT.CNT
  IF PARSE_COLUMN = 1 THEN
     RET_DATA<1,ATT.NXT> = ATT.NXT
  END ELSE
     RET_DATA<1,ATT.NXT> = ENTIRE_RECORD<ATT.NXT>
   END
NEXT ATT.NXT
RETURN
END

:LIST MEMBERS.NEW EVAL 'SUBR("CHG.MEMBER.REC.AGAIN",@RECORD,1)' EVAL 'SUBR("CH
G.MEMBER.REC.AGAIN",@RECORD,2)'

LIST MEMBERS.NEW EVAL "SUBR("CHG.MEMBER.REC.AGAIN",@RECORD,1)" EVAL "SUBR("CHG.M
EMBER.REC.AGAIN",@RECORD,2)" 06:57:23am  16 May 2025  PAGE    1
               SUBR("CHG.MEMBER.REC    SUBR("CHG.MEMBER.REC
MEMBERS.NEW    .AGAIN",@RECORD,1)..    .AGAIN",@RECORD,2)..

0312           1                       Markowski
               2                       Karl
               3                       B
               4                       Mr
               5                       8953 Rowes Drive
               6                       Williston
               7                       DC
0621           1                       Postek
               2                       Scott
               3                       P
               4                       Mr
               5                       13322 West Winter Pl
                                       ace
               6                       Grand Haven
               7                       NY
0930           1                       Sun
               2                       Mike
               3                       U
Press any key to continue...

Hi

When you create your odbc design on server side, uv odbc create @ASSOC_ROW.

System-generated. @ASSOC_ROW appears automatically for any association or unassociated multivalued column or field, unless the dictionary contains an @ASSOC_KEY.mvname X-descriptor that defines an association key. See Association Keys.

I do not find the uvodbc doc file, I attach it Here.

I hope this help

I like how others have done this, for their versatility.  That being said, I have two additional possibilities to consider. 

First option: create an I-Descriptor like below.  This will allow you to list your dictionary that has LOWER(@RECORD), and then this new dictionary, which should give you line numbers.

0001: I
0002: LOWER(@RECORD)
0003: F;0;NV;S
0004: TESTING
0005: 25L
0006: M

Second option: create a subroutine like below.  This would allow you to create an I-Descriptor like "LOWER(SUBR('GETRNG', 1, 1, DCOUNT(@RECORD, @AM)))", whether in an EVAL from TCL or from a dictionary.  I use this method for many things because it is dynamic.  You could change it to use @VM instead of @AM if you do not want to add the LOWER() function.

SUBROUTINE GETRNG(RESULT, START, INCREMENT, TIMES)
    IF UNASSIGNED(INCREMENT) THEN INCREMENT = 0

    RESULT = ''
    IF INCREMENT EQ 0 THEN RETURN
    IF NOT(NUM(START)) THEN RETURN
    IF NOT(NUM(TIMES)) THEN RETURN
    IF NOT(NUM(INCREMENT)) THEN RETURN

    DIM WORK(TIMES)
    XX = START
    FOR II = 1 TO TIMES
        WORK(II) = XX
        XX += INCREMENT
    NEXT II
    MATBUILD RESULT FROM WORK USING @AM
    RETURN
END

------------------------------
Joseph von Arx
Software Developer
Data Management Associates Inc DMA
Cincinnati OH US
------------------------------

Tyrel:

The original expression works perfectly. Unfortunately our version of UniVerse does not support FIELDS(). (PICK flavor 11.2, shortly to be upgraded to 11.3).

I've run into this frustrating problem before and would love to know how to add the function, but that is certainly lightyears beyond my abilities. Seems odd that it isn't included.

Should be easy enough to parse the output in T-SQL though, after it passes through odbc. It would only be run periodically to update and insert to a table in another system, so it isn't a big deal, but would be nice to do it all in UniVerse.

It returns a row for every value in the CONTROL record. Mainly for expanding my knowledge, is there a way to limit the output to just the rows in the data? There are some empty rows that are within the list, so I don't think selecting those that are not empty would not work, as it would put them out of alignment with the CONTROL record.

Manu:

I'm familiar with @ASSOC_ROW used with multivalues defined in a DICT I-descriptor but I don't know how to work with it with LOWER() and particularly to get it into another EVAL when there doesn't seem to be a way for EVALs to relate to each other.

I'm trying to use EVALs in the NATIVE query via odbc to keep everything together in the linked server query. The many hundreds of records in the data file are incredibly diversely structured. I've set up a variety of TRANS entries from another account linked with Q VOC entries and special DICT items to handle some of these structures, but it is getting hard to manage as I add more. It causes a real mess if the queries are not limited to the right record.

It is a hideously inconvenient way that the third party supplier has so many different record structures in the same file. I expect it is a product of multiple teams using the same file for a huge diversity of purposes. I'm not fussed by the fact they have used the attribute number as data in this structure.

Sadly, it isn't their worst abomination. I was aghast when I discovered they also stored data in DICT records using a variety structures that obviously shouldn't ever be in a DICT. Clearly somebody knew enough to be dangerous.

Jonathan:

I like your solution for its versatility. I'm contemplating further generalising the subroutine to also use the record ID as a parameter, thinking it might then be able to handle a variety of the different structures. 

The software supplier does something like this on the same file and others, but they limit the output by including a parameter for the code to be looked up and returning a single value, whereas I want the entire set to put through odbc. 

SUBROUTINE GETRNG(RESULT, START, INCREMENT, TIMES)
    IF UNASSIGNED(INCREMENT) THEN INCREMENT = 0

    RESULT = ''
    IF INCREMENT EQ 0 THEN RETURN
    IF NOT(NUM(START)) THEN RETURN
    IF NOT(NUM(TIMES)) THEN RETURN
    IF NOT(NUM(INCREMENT)) THEN RETURN

    DIM WORK(TIMES)
    XX = START
    FOR II = 1 TO TIMES
        WORK(II) = XX
        XX += INCREMENT
    NEXT II
    MATBUILD RESULT FROM WORK USING @AM
    RETURN
END

Joseph:

That looks interesting but I don't understand the conversion expression in the I descriptor. 

Greg,

The conversion I provided is an F-Correlative to count the number of values in the lowered record.  The fcode correlative should be in your documentation or it should be accessible on Rocket's documentation site.

If the first example is confusing, the second example I provided is using a straight I-Descriptor format.