Hi Guys,
got a conundrum for you !!!
one of my users wanted a simple report to extract details for a part that needed data from the main report file containing most of the required data but also from another file in the same data account. BTW. We are Universe users
The details in the local file are all single values, however the field for data from the other file is multivalued and the user only needs the first multi-value from the second field in the record in the other file.
as this request was likely to be a one off, which I thought would a simple quick and dirty job, I did not want to bother with a sub-routine or create a program to explode the MV's in the other file into a workfile first or indeed write a program to do the whole report which needed to go into Excel.
So I naively assumed that extracting this detail would be easily doable with I Descriptors.
this is what the data record looks like in the second file
0001: 04551VM14550VM04913VM04912
0002:
15143VM15148VM15150VM15160
0003: 12VM12VM12VM12
0004: 1800000VM1848000VM1776000VM1764000
As you can see a simple 4 field record. NB: Some of these records have 100's of MV's
I want extract Attribute<2,1>
15143In the host file for this data this works and correctly displays 15143
0001: I
0002: FIELD(CPART,@VM,1,1) [cpart is attribute 2]
0003:
0004: CUST PART NO
0005: 30L
0006: S
So in the dictionary of the file I want to report on this also works to fetch all 4 values from attribute <2>
0001: I
0002: TRANS(FILENAME,PARENT.PART.NBR,CPART,"X")
0003:
0004: CUST PART NO
0005: 30L
0006: S
BOM KEY... CUST PART NO.
216962 15143
15148
15150
15160
Naturally I thought that this would also work !!!!!
0001: I
0002: FIELD(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S
WRONG it displays all the MV's
BOM KEY... CUST PART NO..
216962 15143
15148
15150
15160
I then changed it to FIELDS and it made no difference
0001: I
0002: FIELDS(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S
BOM KEY... CUST PART NO.
216962 15143
15148
15150
15160
Now at this stage I realised that as FIELD and FIELDS was not going to work, I went searching in the UniVerse_BASICCommandsReference_V1134.pdf to explore for alternative commands that may work
so I tried
CONVERT in changing the @VM to "*" and then wanted to apply a FIELD around that, the CONVERT I Description Complied,
0001: I
0002: FIELD(CONVERT(@VM,"*",(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"))),"*",1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S
but still just returned
BOM KEY... CUST PART NO..
216962 15143
15148
15150
15160
Tried
CHANGE that won't compile in an I descriptor
Tried
REPLACE and did not get anything sensible
I have had some success with
EXTRACT0001: I
0002: EXTRACT(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S
This does deliver the required output, put very annoyingly it displays on screen
Program ".ITYPE.": pc = 1E, Nonnumeric data when numeric required. Zero used.
For each row of output data, this is annoying, However I've hidden it from the screen with a HUSH, if this report paragraph ever sees the light of day again
Just trying to save others from going through the same hoops I've tried, in the end it would have been much quicker to write a quick Basic Program, but, sometimes, obstinacy takes over…..
I have got the User her data into a CSV file, but I would like to ask of you all, have you a neat simple solution for extracting a Single MV value from another file just using an I descriptor or 2, so it can be used in Retrieve paragraph ???
Thanks in advance
Andrew
------------------------------
Andrew Milne
Business Systems Manager
Potter and Moore Innovations
Peterborough, Cambs GB
------------------------------