Skip to main content
Hello folks,

A never answered behavior : how to mix monovalued and multivaluled data into a SREFORMAT result. 

Here is the a sample uniQuery, SORT + WITH + WHEN + BY.EXP on HS.SALES (demo account into uv)
I filter data on STATE (monovalue field) and BUY_DATE (multivalued field) and I display data from monovalued and multivalued field.
As expected, with SORT BY.EXP, the monovalued field are replicated on each occurence of multivalued line.

SORT CUSTOMER WITH STATE = "MA" AND WHEN BUY_DATE >= "20/01/91" AND <= "29/01/91" BY.EXP BUY_DATE BY CUSTID CUSTID STATE CITY BUY_DATE PRODID ID.SUP
Customer ID State City........ Date Purchased Product
8 MA Lowell 21/01/91 M3000
8 MA Lowell 21/01/91 S3000
2 MA Waltham 22/01/91 S3000
3 records listed.
​

so, now I'll send these result to a temporary file with SREFORMAT, which build a record by line of result ; the first column is the key on the temp file.
I use the same phrase, replace the SORT verb by SREFORMAT.

CREATE.FILE FTEMP 18 53 4
SREFORMAT CUSTOMER WITH STATE = "MA" AND WHEN BUY_DATE >= "20/01/91" AND <= "29/01/91" BY.EXP BUY_DATE BY CUSTID CUSTID STATE CITY BUY_DATE PRODID ID.SUP
File Name = FTEMP​

so, when I look inside the temp file, I read there is no value for monovalued field.
>CT FTEMP *
8
0001
0002
0003 21/01/91
0004 S3000
2
0001
0002
0003 22/01/91
0004 S3000​


I try to define DICT CUSTID and STATE with a lot of tricks (REUSE, SUBR, build it as part of the ORDER assoc, ... ) I can't get any data in my temp file.
Bonus, why only one multivalue on record id 8 then SORT show two lines !! 

Enjoy the challenge :-)


Hello folks,

A never answered behavior : how to mix monovalued and multivaluled data into a SREFORMAT result. 

Here is the a sample uniQuery, SORT + WITH + WHEN + BY.EXP on HS.SALES (demo account into uv)
I filter data on STATE (monovalue field) and BUY_DATE (multivalued field) and I display data from monovalued and multivalued field.
As expected, with SORT BY.EXP, the monovalued field are replicated on each occurence of multivalued line.

SORT CUSTOMER WITH STATE = "MA" AND WHEN BUY_DATE >= "20/01/91" AND <= "29/01/91" BY.EXP BUY_DATE BY CUSTID CUSTID STATE CITY BUY_DATE PRODID ID.SUP
Customer ID State City........ Date Purchased Product
8 MA Lowell 21/01/91 M3000
8 MA Lowell 21/01/91 S3000
2 MA Waltham 22/01/91 S3000
3 records listed.
​

so, now I'll send these result to a temporary file with SREFORMAT, which build a record by line of result ; the first column is the key on the temp file.
I use the same phrase, replace the SORT verb by SREFORMAT.

CREATE.FILE FTEMP 18 53 4
SREFORMAT CUSTOMER WITH STATE = "MA" AND WHEN BUY_DATE >= "20/01/91" AND <= "29/01/91" BY.EXP BUY_DATE BY CUSTID CUSTID STATE CITY BUY_DATE PRODID ID.SUP
File Name = FTEMP​

so, when I look inside the temp file, I read there is no value for monovalued field.
>CT FTEMP *
8
0001
0002
0003 21/01/91
0004 S3000
2
0001
0002
0003 22/01/91
0004 S3000​


I try to define DICT CUSTID and STATE with a lot of tricks (REUSE, SUBR, build it as part of the ORDER assoc, ... ) I can't get any data in my temp file.
Bonus, why only one multivalue on record id 8 then SORT show two lines !! 

Enjoy the challenge :-)


The first issue is that the value defined for the target key is one of your single-value fields. Therefore, the second MV from the originating record will overwrite the first according to your BY.EXP expression. So, you need to define a "MVID" Itype like CATS(CATS(CATS(CATS(BUY_DATE,REUSE('*')),REUSE(CUSTID)),REUSE('*')),PRODID))

The next problem was that the SV output fields only populates the first output record AFAIK.

Using SQL with UNNEST instead of REFORMAT avoids the problem.

First define the target file SV dictionaries (unfortunately UV SQL doesn't auto create columns like MSSQL's INSERT INTO does).
ED DICT FTEMP CUSTID STATE BUY_DATE PRODID etc
then 
INSERT INTO FTEMP (@ID,CUSTID,STATE,BUY_DATE,PRODID) SELECT MVID, CUSTID, STATE, BUY_DATE, PRODID FROM UNNEST CUSTOMER ON ORDERS WHERE STATE = 'MA' AND BUY_DATE >= '20/01/91' AND BUY_DATE <= '27/01/91';

LIST.ITEM FTEMP

8422*8*M3000
001 8
002 MA
003 8422
004 M3000

8422*8*S3000
001 8
002 MA
003 8422
004 S3000

8423*2*S3000
001 2
002 MA
003 8423
004 S3000

Terms & ConditionsAccessibility statement