I have a report that needs to be able break out comma separated data that is in a row and do a query based on these which will ultimately produce a report where the data is broken out into rows.

For example if this is the source

  
   id      data
   --      ----
   test   1,2,3 

This is the reference

  
   id      data
   --      ----
   1       ref 1 
   2       ref 2
   3       ref 3

Would produce a report like

  
 test   ref 1
 test   ref 2
 test   ref 3

I can't quite figure out how to do this in FOCUS is there a way?

If I was doing this in JAVA I would be able to use Hashtables and arrays to gather information and merge them.
Can I send the output of the first query to JAVA and then use its output to generate the second query as well as the report?

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

Perhaps you could look at the user written subroutines and use those to parse out the row - You will be able to find the data separated by commas and put those into discrete fields --- check it out...of course, I am certain you will discover other ways as well.

Good luck

What is the maximum number of commas in your data field?
What is the maximum size of each number?

Assumptions.
Your file:

  
testa   1,2,3
testb   12
testc   123,2,4
testd   1,2,3,4,5,1234,134,13,10

The Master for this file

  
FILENAME=MANISH, SUFFIX=FIX, 
DATASET=FOCALPOINT/MANISH.TXT, $
 SEGNAME=MANISH, SEGTYPE=S0
  FIELDNAME=TNAME, FORMAT=A8, ACTUAL=A8, $
  FIELDNAME=TDATA, FORMAT=A30, ACTUAL=A30, $

Maximum number of values: 10
Maximum length of each number: 5

The fex to split the TDATA field:

  
-* File manish.fex
-SET &MAXN=10;
-SET &MAXS=5;
DEFINE FILE MANISH
-REPEAT #SPLIT FOR &I FROM 1 TO &MAXN;
NUM&I / I&MAXS.S = EDIT(GETTOK(TDATA, 30, &I, ',', &MAXS, 'A&MAXS'));
-#SPLIT

END
TABLE FILE MANISH
PRINT 
-REPEAT #P FOR &I FROM 1 TO &MAXN;
NUM&I 
-#P
BY TNAME
ON TABLE SAVE AS MTEMP 
END
-RUN

The MTEMP master:

  
FILENAME=MTEMP   , SUFFIX=FIX  
  SEGMENT=MTEMP, SEGTYPE=S0
    FIELDNAME=TNAME, ALIAS=E01, USAGE=A8, ACTUAL=A08, $
  SEGMENT=MDATA, PARENT=MTEMP, OCCURS=VARIABLE, $
    FIELDNAME=NUM, ALIAS=E02, USAGE=I5S, ACTUAL=A05, $

The output from MTEMP:

TABLE FILE MTEMP
PRINT *
IF NUM NE 0
END

From there you can join each value to the reference table.

Good luck!