HI all, I have a conceptional question which may or may not be possible. (not sure if I worded it right for my initial search)

I have a file with multiple fields of the same data, i.e. color.

For simplicity, lets say it has 21 fields. A sku number, and 20 color fields.
SKUNUMBER
COLOR001
COLOR002
COLOR003
...
COLOR020

Is there any way, in a Master file, to seperate these into 20 records, each having the sku_number and a single color field?
i.e. sku color001
sku color002
....
sku color020

I could do this using SQL and multiple passes with a hold file, but I am looking for a master file solution if possible.

and, i'll state up front, I have very little experience with Master file creation.

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

I think you cannot do that with a database table, but you can with a HOLD file. First create a hold file with data extracted from your database table. Then create a Master "on the fly" in the report fex or as a stand alone master. Then use FILEDEF to reassign which master you want to use with the hold file.

Here's a working example:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA


-* create a test hold file of skus and colours 
FILEDEF SKUS DISK skus.ftm
-RUN

-WRITE SKUS 1234 RED       GREEN     YELLOW    BLUE      PINK      ORANGE    SILVER    GOLD      MAROON    BROWN
-WRITE SKUS 3456 PINK      STEEL     SILVER    BLACK     TAUPE     AZURE     YELLOW    MOSS      AQUA      LIGHT BLUE
-RUN

-* create a master for the test hold file 
FILEDEF DATAMAST DISK skus.mas
-RUN

-WRITE DATAMAST
-WRITE DATAMAST FILE=SKUS, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=SKU_NUMBER, ALIAS=SKU, USAGE=A5, ACTUAL=A5, $
-WRITE DATAMAST   FIELD=COLOR001, ALIAS=COL001 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR002, ALIAS=COL002 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR003, ALIAS=COL003 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR004, ALIAS=COL004 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR005, ALIAS=COL005 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR006, ALIAS=COL006 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR007, ALIAS=COL007 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR008, ALIAS=COL008 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR009, ALIAS=COL009 , USAGE=A10, ACTUAL=A10, $
-WRITE DATAMAST   FIELD=COLOR010, ALIAS=COL010 , USAGE=A10, ACTUAL=A10, $

-* create the report with sku and all colours in the same row
TABLE FILE SKUS
PRINT
*
HEADING
"REPORT 1"
END
-RUN

-* create a master for the hold file, with occurs
FILEDEF DATAMAST DISK SKUS_OCC.MAS
-RUN

-WRITE DATAMAST
-WRITE DATAMAST FILE=SKUS_OCC, SUFFIX=FIX, $

-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=SKU_NUMBER, ALIAS=SKU, USAGE=A5, ACTUAL=A5, $

-WRITE DATAMAST SEGNAME=SEG2, PARENT=SEG1, OCCURS=20, $
-WRITE DATAMAST   FIELD=COLOUR, ALIAS=COL , USAGE=A10, ACTUAL=A10, $

-* reassign the test hold file to the master with occurs
FILEDEF SKUS_OCC DISK skus.ftm
-RUN

-* create the report with sku and individual colours
TABLE FILE SKUS_OCC
PRINT
SKU_NUMBER
COLOUR
HEADING
"REPORT 2"
END
-RUN

OCCURS is probably the best way.

You can also have the OCCURS as variable if you do not know the number of fields.

I would suggest checking out the documentation on the concepts.

Hi Francis,
Thanks very much for that, it definitely points me in the right direction. There is one issue I am having with your example, it only returns 19 records. For some reason it's dropping BROWN from the first record.

Now that I have an idea of how to do this, I should be OK.

Waz, yes, I definitely got some reading to do. Frankly, it's about time for me to learn about these master file thingy's.

Master Files can do some really amazing things.

Francis, thanks so much for your help. Your solution worked perfectly for what I needed.

The reality of my need was I have four groups with repetitive fields, 3 occurs, 40 occurs, 20 occurs, and 25 occurs. If I had to rely on SQL for this I'd probably go nuts....but the occurs made it quite easy.

The problem in my original example is due to the fact that I was generating the demo HOLD file with WRITE statements and the length of the first row of data was less than what the master declares. I can probably fix that with a variable block file or column. It won't occur in the real world because you have a real HOLD file.

I'm glad the occurs works for you.