I am looking for a way to create multiple rows for a single value of a field. I thought some sort of decode function would work but it is only reading the first decode.

I will provide an example.

If I have fieldA with values

RED
YELLOW
GREEN
BLACK

and fieldB with values
1
2
3
4

What I am trying to do is have the following print.

FieldA FieldB DefineFieldA
RED 1 Red - Urgent
RED 1 Red - Medium
RED 1 Red - Low
YELLOW 2 Yellow - Urgent
YELLOW 2 Yellow - Medium
GREEN 3 Green
BLACK 4 Black


So I start with 4 values for fieldA and would like to end up with 7.

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

Are these fields in one or in two tables?
And if in two, is there a relation between them?

FieldA and FieldB are in the same table. The definefieldA is a created designation to fieldA.

Does that make sense?

Sounds like a job for McGyver!
Just search for mcgyver on this forum.

If I understand you correctly, when fieldB = 1 you want 3 records; when 2, you want 2 records; otherwise 1 record.
True?
If yes, then use McGuyver.

Peechy, try this:

  
-* File peechy01.fex
JOIN BLANK WITH SEATS IN CAR TO BLANK IN FSEQ AS MCG
DEFINE FILE CAR
BLANK/A1 WITH SEATS = ' ';
DEFBOD/A6 = IF SEATS EQ 2 AND COUNTER EQ 1 THEN 'URGENT' ELSE 
            IF SEATS EQ 2 AND COUNTER EQ 2 THEN 'MEDIUM' ELSE
			IF SEATS EQ 2 AND COUNTER EQ 3 THEN 'LOW'    ELSE
			IF SEATS EQ 4 AND COUNTER EQ 1 THEN 'URGENT' ELSE
			IF SEATS EQ 4 AND COUNTER EQ 2 THEN 'MEDIUM' ELSE 
			IF SEATS EQ 4 AND COUNTER EQ 3 THEN 'NONE'   ELSE
			IF SEATS EQ 5 AND COUNTER EQ 1 THEN ' '      ELSE 'NONE';
END
TABLE FILE CAR
PRINT DEFBOD
BY CAR
BY MODEL
BY BODYTYPE 
BY SEATS
IF COUNTER LE 3
IF DEFBOD NE NONE
END

The FSEQ file.
MASTER

  
FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.DAT, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

DATA

  
 SOMECHARACTERSFORTHEFSEQFILENOTICETHEFIRSTCHARACTERISASPACE

I don't know if this is possible with your data set...?

TABLE FILE CAR
	BY HIGHEST 4 COUNTRY
ON TABLE HOLD AS X
END

TABLE FILE X
	PRINT COUNTRY
ON TABLE HOLD AS Y
MORE
FILE X
WHERE COUNTRY EQ 'W GERMANY' OR 'JAPAN'
MORE
FILE X
WHERE COUNTRY EQ 'W GERMANY'
END

TABLE FILE Y
	PRINT COMPUTE COUNTER/I1 = IF LAST FST.COUNTRY EQ FST.COUNTRY THEN COUNTER + 1 ELSE 1;
	BY COUNTRY
END

McGyver™

G'luck

I will give these suggestions a try and see if it works for what I am doing. Thank you.

One quick follow up. Where do I put the data element of this? I know where to create the procedure and how to save a master file. Where does the data section that is referenced by Danny-SRL go?

I would place the mcguyver in baseapp so you can use it every time

Very true!

I generally use this variation of the master:

 
 $ThisStructureIsUsedToImplementTheMcGyverTechniqueAndShouldNotBeAltered888888881111111111111111111111111111111111111111111111111111111111111111111111
FILENAME =MACG,SUFFIX =FIX, DATASET=baseapp/macg.mas,$
SEGNAME  =ROOT   ,SEGTYPE=S0
FIELDNAME=BLANK  ,           , FORMAT=A1,ACTUAL=A1,$
SEGNAME  =BABY   ,SEGTYPE=S0 , OCCURS=149
FIELDNAME=DUMMY  ,           , FORMAT=A1,ACTUAL=A1,$
FIELDNAME=COUNTER,ALIAS=ORDER, FORMAT=I4,ACTUAL=I4,$

Save it as macg.mas in baseapp.
It would work the same way "programmatically" i.e. instead of FSEQ just use MACG.
But it is a "cheat" in the sense that the master file IS the data file, and therefore one only needs one file.
The trick is that only the first row may have a blank character in the first column.

So I tried a couple of the suggestions above and was unable to have any records returned. I did try a different technique posted and it seemed to work. Not really sure what I was doing wrong the first time around. Here is what my end code ended up being.

-*******************************************************************************************
-*DECODE FILE
-*******************************************************************************************
DEFINE FILE T_SQL_SERVICE
IND/I10 WITH DESCR = IND + 1;
BLANK/A1 WITH DESCR = '';
END
-RUN

TABLE FILE T_SQL_SERVICE
PRINT DESCR
IND
BLANK
ON TABLE HOLD AS TMP_FILE FORMAT FOCUS
END
-RUN
EX -LINES 4 EDAPUT MASTER,blank_mas,CV,FILE
FILENAME=BLANK_MAS, SUFFIX=FIX,$
SEGNAME=BLANK_MAS, $
FIELD=FIELD1 ,ALIAS= ,A1 ,A1 ,$

FILEDEF BLANK_MAS DISK blank_mas.mas (RECFM F LRECL 1

DEFINE FILE BLANK_MAS
CNT/I3 WITH FIELD1 = CNT + 1;
BLANK/A1 WITH FIELD1 = '';
END
-RUN

-* 8 - ORDER OF DUPLICATION
TABLE FILE BLANK_MAS
PRINT
FIELD1 NOPRINT
BLANK
WHERE CNT LE 10
BY BLANK NOPRINT
ON TABLE HOLD AS TMP_MAS1 FORMAT FOCUS INDEX BLANK
END
-RUN

JOIN BLANK IN TMP_FILE TAG T1 TO MULTIPLE BLANK IN TMP_MAS1 TAG T2 AS JJJ

DEFINE FILE TMP_FILE
DSERV/A40 = IF DESCR EQ 'Alarm' AND T2.FOCLIST EQ 1 THEN 'Burglar Alarm' ELSE
IF DESCR EQ 'Alarm' AND T2.FOCLIST EQ 2 THEN 'Fire Alarm' ELSE
IF DESCR EQ 'Alarm' AND T2.FOCLIST EQ 3 THEN 'Fire Alarm, C-UL' ELSE
IF DESCR EQ 'Alarm' AND T2.FOCLIST GT 3 THEN '' ELSE
IF DESCR EQ 'Building Structure/Exterior Walls' AND T2.FOCLIST EQ '1' THEN 'Exterior Walls' ELSE
IF DESCR EQ 'Building Structure/Exterior Walls' AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR EQ 'Curbs/Sidewalks' AND T2.FOCLIST EQ 1 THEN 'Curb' ELSE
IF DESCR EQ 'Curbs/Sidewalks' AND T2.FOCLIST EQ 2 THEN 'Sidewalk' ELSE
IF DESCR EQ 'Curbs/Sidewalks' AND T2.FOCLIST GT 2 THEN '' ELSE
IF DESCR EQ 'Detention Pond/Catch Basin' AND T2.FOCLIST EQ 1 THEN 'Catch Basin' ELSE
IF DESCR EQ 'Detention Pond/Catch Basin' AND T2.FOCLIST EQ 2 THEN 'Detention Basin' ELSE
IF DESCR EQ 'Detention Pond/Catch Basin' AND T2.FOCLIST GT 2 THEN '' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 1 THEN 'Automatic Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 2 THEN 'Cart Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 3 THEN 'Emergency Exit Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 4 THEN 'Entrance Exit' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 5 THEN 'Mall Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 6 THEN 'Manual Entrance Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 7 THEN 'Team Member Entrance' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST GT 7 THEN '' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 1 THEN 'Air Handler' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 2 THEN 'Chillers' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 3 THEN 'Cooling tower' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 4 THEN 'Exhaust Fan' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 5 THEN 'Forced Air Furnace' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 6 THEN 'HVAC' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 7 THEN 'HVAC Controls' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 8 THEN 'HVAC Equipment' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST GT 8 THEN '' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 1 THEN 'Beds - Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 2 THEN 'Beds - Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 3 THEN 'Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 4 THEN 'Grass - Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 5 THEN 'Grass - Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 6 THEN 'Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 7 THEN 'Plants - Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 8 THEN 'Plants - Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 9 THEN 'Trees - Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 10 THEN 'Trees - Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST GT 10 THEN '' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 1 THEN 'Crosswalk' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 2 THEN 'Fence' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 3 THEN 'Fire Hydrant' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 4 THEN 'Parking Lot' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 5 THEN 'Parking Lot Drive' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 6 THEN 'Parking Lot Landscape' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 7 THEN 'Parking Stalls' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 8 THEN 'Retaining Wall' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST GT 8 THEN '' ELSE
IF DESCR EQ 'Roof' AND T2.FOCLIST EQ 1 THEN 'Roof System' ELSE
IF DESCR EQ 'Roof' AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR EQ 'Signage - Building' AND T2.FOCLIST EQ 1 THEN 'Building Sign' ELSE
IF DESCR EQ 'Signage - Building' AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR EQ 'Signage - Directional' AND T2.FOCLIST EQ 1 THEN 'Directional Sign' ELSE
IF DESCR EQ 'Signage - Directional' AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR IN ('Signage - Pylon','Signage - Pylon and/or Monument') AND T2.FOCLIST EQ 1 THEN 'Pylon or Monument Sign' ELSE
IF DESCR IN ('Signage - Pylon','Signage - Pylon and/or Monument') AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 1 THEN 'Landscape Lighting' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 2 THEN 'Lighting Motion Sensor' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 3 THEN 'Natural Gas Pipe' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 4 THEN 'On site Sewer system' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 5 THEN 'Parking Lot Lighting' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 6 THEN 'Perimeter/Security Lighting' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST GT 6 THEN '' ELSE
IF DESCR EQ 'Utilities - Interior' AND T2.FOCLIST EQ 1 THEN 'Backflow prevention' ELSE
IF DESCR EQ 'Utilities - Interior' AND T2.FOCLIST EQ 2 THEN 'Boilers' ELSE
IF DESCR EQ 'Utilities - Interior' AND T2.FOCLIST EQ 3 THEN 'Electrical Panels' ELSE
IF DESCR EQ 'Utilities - Interior' AND T2.FOCLIST GT 3 THEN '' ELSE
DESCR;
END
TABLE FILE TMP_FILE
SUM
DESCR
DSERV
BY DESCR NOPRINT
BY DSERV NOPRINT
WHERE NOT DSERV EQ ''
ON TABLE HOLD AS LISTING FORMAT FOCUS INDEX DESCR
END
-RUN
JOIN
ID IN T_SQL_PROPERTY TO
ID IN T_SQL_SERVICES AS J02
END
JOIN
T_SQL_SERVICES.SERVICE IN T_SQL_PROPERTY TO
SERVICE IN T_SQL_SERVICE AS J03
END
JOIN
T_SQL_SERVICES.REPAIR IN T_SQL_PROPERTY TO
TENANTTYP IN T_SQL_TENANTTY TAG J05 AS J05
END
JOIN
LEFT_OUTER
T_SQL_SERVICE.DESCR IN T_SQL_PROPERTY TO MULTIPLE
DESCR IN LISTING AS J04
END
TABLE FILE T_SQL_PROPERTY
SUM
DSERV AS 'Service'
T_SQL_SERVICE.DESCR
MAX.J05.DESCR AS 'Repairs'
BY PROPID NOPRINT
BY BLDGID NOPRINT
BY ID NOPRINT
BY T_SQL_SERVICE.DESCR NOPRINT
BY DSERV NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END

This seems to give me the result I am looking for. Not sure if there was a more efficient way of getting the same result. Thanks for your help.