I have a database that holds among others the next two fields

clientid verylongstring
12345    '991234','993221','914727','936269','939292'
82836    '921438','913432','915347'
82805    '991234','953221','913432','915347','914727','933269','939292'

So in fact the second long field holds several values.

I need to build a report as if the several numbers in the long string are records in a database.

Is it possible to do something like a decode on that string or build a report like

12345    '991234'
12345    '993221'
12345    '914727'
12345    '936269'
12345    '939292'
82836    '921438'
82836    '913432'
82836    '915347'
82805    '991234'
82805    '953221'
etc

Try creating a temporary FOCUS file and insert records into it using the MODIFY FILE command along with the FREEFORM statement.
The FREEFORM statement reads comma-delimited data, you'll first have to save your data.

define file xxx
commma/a1 = ',';
endofline = '$';
end

table file xxx
print cliendid comma verylongstring comma endofline
on table save
end

Now use a MODIFY FILE with FREEFORM to read the save file.

Regards,

How about creating a master with OCCURS?

If the original table is an RDBMS, create a HOLD file and then use a different master with OCCURS.

Here's an example:

FILE=HATTRIB          ,SUFFIX=FIX
SEGNAME=HATTRIB
FIELDNAME   =NODE_ID    ,E01         ,P11      ,A11      ,$
FIELDNAME   =WTEXT      ,E02         ,A1560     ,A1560   ,$
SEGNAME=ATTRIB          ,PARENT=HATTRIB,  POSITION=WTEXT,  OCCURS=15
FIELDNAME   =ATTRIB_KEY      ,E04         ,A04     ,A04     ,$
FIELDNAME   =WCOL            ,E05         ,A100    ,A100    ,$
FIELDNAME   =ALINE_NO        ,ORDER       ,I5       ,I5     ,$

Isn't this a good example of where to use McGuyver? Split up verylongstring into multiple fields using GETTOK and then use Mcguyver to print them out one over the other.

I think this would be nice - if IBI built in MCGUYVER technique directly into wf. The MULTIPLY command would have syntax and usage similar to the JOIN command.

MULTIPLY filename[.segname] n TIMES

This would mean the record retrieved once and then passed n times to the matrix with dummy field filename.segname.MULTIPLE which varied from 1 to n.

This would give wf a lot of extra functionality with a relatively simple implementation.

Once you're done: MULTIPLY CLEAR !
And ? MULTIPLY

I can only dream on...

This message has been edited. Last edited by: hammo1j,

Try the following:

FILE=SPLIT ,SUFFIX=FIX,$
SEGNAME=ONE ,SEGTYPE=S0,$
FIELDNAME =CLIENTID , ,ACTUAL=A5 ,USAGE=A5 , $
SEGNAME=TWO ,SEGTYPE=S0, PARENT=ONE, OCCURS=20 , $ <=== Your maximum value
FIELDNAME = SHORTSTRING , ,ACTUAL=A8 ,USAGE=A8 , $
FIELDNAME = COMMA , ,ACTUAL=A1 ,USAGE=A1 , $

DEFINE FILE UNSPLIT
-*
-* 20 Occurences * 9 bytes + 5 bytes for the Client ID = 185 bytes
-*
TOTALLINE/A185 = CLIENTID | LONGSTRING ;
END

TABLE FILE UNSPLIT
PRINT TOTALLINE
ON TABLE SAVE AS SPLIT
END

TABLE FILE SPLIT
PRINT SHORTSTRING
BY CLIENTID
WHERE SHORTSTRING NE ' '
END

Output Results:

CLIENTID SHORTSTRING
-------- -----------
12345 '991234'
'993221'
'914727'
'936269'
'939292'
82805 '991234'
'953221'
'913432'
'915347'
'914727'
'933269'
'939292'
82836 '921438'
'913432'
'915347'

Good Luck.

This looks great Albert

When I posted my question I thought al the numbers in the longstring where of equal length, but in the meantime I found that they are not, some are 7 and some are 9.
I will try to combine some tips here and see if I will get it work

Thanks

If your field lengths are different then try this:

The Input file is as follows:

Client ID Long Value
--------- ----------
12345 '9234','9932','97','936269','939292'
82836 '921438','913432','917'
82805 '991234','953221','912','915347','917','969','939292'

Master file is now (similar to earlier post -- no COMMA field in SEGNAME TWO:

FILE=SPLIT ,SUFFIX=FIX, $
SEGNAME=ONE ,SEGTYPE=S0,$
FIELDNAME = CLIENTID , ,ACTUAL=A5 ,USAGE=A5 , $
SEGNAME=TWO ,SEGTYPE=S0, PARENT=ONE, OCCURS=20
FIELDNAME = SHORTSTRING , ,ACTUAL=A9 ,USAGE=A9 , $

Focexec is:

DEFINE FILE UNSPLIT
GTOK1 /A9 = GETTOK(LONGSTRING,101,1,',',9,GTOK1);
GTOK2 /A9 = GETTOK(LONGSTRING,101,2,',',9,GTOK2);
GTOK3 /A9 = GETTOK(LONGSTRING,101,3,',',9,GTOK3);
GTOK4 /A9 = GETTOK(LONGSTRING,101,4,',',9,GTOK4);
GTOK5 /A9 = GETTOK(LONGSTRING,101,5,',',9,GTOK5);
GTOK6 /A9 = GETTOK(LONGSTRING,101,6,',',9,GTOK6);
GTOK7 /A9 = GETTOK(LONGSTRING,101,7,',',9,GTOK7);
-*
TOTALLINE/A185 = CLIENTID |
GTOK1 | GTOK2 | GTOK3 | GTOK4 | GTOK5 | GTOK6 |GTOK7;
END

TABLE FILE UNSPLIT
PRINT TOTALLINE
ON TABLE SAVE AS SPLIT
END

TABLE FILE SPLIT
PRINT SHORTSTRING
BY CLIENTID
WHERE SHORTSTRING NE ' '
END

Produces the following:

CLIENTID SHORTSTRING
-------- -----------
12345 '9234'
'9932'
'97'
'936269'
'939292'
82805 '991234'
'953221'
'912'
'915347'
'917'
'969'
'939292'
82836 '921438'
'913432'
'917'

I think this is what you're looking for.