Hi all,

I have a hold table that contains the following data:

  
COL1  COL2  COL3  COL4
----  ----  ----  ----
1     2     3     4
5     6     7     8

I need to convert the COL columns into a rows. For example the result should be the same as it is shown below:

 
COL
----
1       <- COL1
2       <- COL2
3       <- COL3
4       <- COL4
5       <- COL5
6       <- COL6
7       <- COL7
8       <- COL8
 

Do you have any idea?

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

we had the same problem and ended up using a series of sql Union statements to get the final product.

I didn't think to ask on here at the time. Just letting you know that if it isn't possible in webfocus, you can do what you need to with sql.

Simple method as you don't give too many details (and probably haven't done any homework) -

FILEDEF LGMAS DISK LGDATA.MAS
FILEDEF LGDATA DISK LGDATA.FTM
-RUN
-WRITE LGMAS FILE=LGDATA,SUFFIX=COM
-WRITE LGMAS SEGNAME=SEG1
-WRITE LGMAS FIELD=COL1, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL2, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL3, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL4, ,I9 ,I9 , $
-WRITE LGDATA 1,2,3,4,$
-WRITE LGDATA 5,6,7,8,$
-RUN
TABLE FILE LGDATA
PRINT COL1
 OVER COL2
 OVER COL3
 OVER COL4
END

T

quote:

TABLE FILE LGDATA
PRINT COL1
OVER COL2
OVER COL3
OVER COL4
END

Thanks Tony A but your example just modify the report output. I want to save the result into the hold table as separate column

L_G,

That's what I meant by you not giving too many details. You didn't say that you wanted to hold the data! Try and be more specific in your question.

Try this -

FILEDEF LGMAS DISK LGDATA.MAS
FILEDEF LGDATA DISK LGDATA.FTM
-RUN
-WRITE LGMAS FILE=LGDATA,SUFFIX=COM
-WRITE LGMAS SEGNAME=SEG1
-WRITE LGMAS FIELD=COL1, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL2, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL3, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL4, ,I9 ,I9 , $
-WRITE LGDATA 1,2,3,4,$
-WRITE LGDATA 5,6,7,8,$
-RUN
SQL
SELECT COL1
  FROM LGDATA
UNION
SELECT COL2
  FROM LGDATA
UNION
SELECT COL3
  FROM LGDATA
UNION
SELECT COL4
  FROM LGDATA
;
HOLD AS Rxxx_H1

Yes, it is SQL against a comma delimited file (but it works), but not knowing what source data yours is it is hard to help you.

T

You can also use the famous McGyver Technique... should be easy to search for McGyver in the forum or even the entire ibi site. Basically, it allows you to output multiple rows with one input row.

So in your original 'hold creation' are you using across to get the data or just a simple print statement? And additionally, is it really just 8 data items or are there many more that you want to become a column of data in a hold file?

Hi,

I am not using the across...

And the data is not only 8 data items, it is just an example.

Currently I am using the McGyver technique and it works fine but i am asking because I think that there is another easy method...

L_G

if you show us the master of this table, that might be helpful.

You could also do this in Dialog Manager using a -REPEAT, -READ NOCLOSE, -WRITE NOCLOSE.

-SET &EOF = 'N';
-REPEAT RWLOOP UNTIL &EOF = 'Y'
-READ INPUT NOCLOSE &VAR1.A8
-SET &EOF=IF &RETCODE EQ 0 THEN 'N' ELSE 'Y';
-IF &EOF EQ 'Y' THEN GOTO -RWLOOP;
-WRITE OUTPUT NOCLOSE &VAR1
-RWLOOP
-ENDREPEAT
-CLOSE INPUT
-CLOSE OUTPUT

This is my first time to use the Mcgyver technique. In this technique,

JOIN BLANK WITH RPH IN CAR TO BLANK IN FSEQ AS AJ
DEFINE FILE CAR
BLANK/A1 WITH RPH = ' ' ;
VERBOBJ/D7 = IF COUNTER EQ 1 THEN RCOST ELSE
IF COUNTER EQ 2 THEN DCOST ELSE
IF COUNTER EQ 3 THEN SALES ELSE
IF COUNTER EQ 4 THEN MPG ELSE
IF COUNTER EQ 5 THEN RPH ELSE 0;
TITLE_VO/A13 = DECODE COUNTER (1 'RETAIL_COST' 2 'DEALER_COST'
3 'SALES' 4 'MILES_PER_GAL'
5 'REV_PER_HOUR' ELSE ' ');
END
TABLE FILE CAR
SUM VERBOBJ AS ' '
ACROSS SEATS
ACROSS BODYTYPE
BY COUNTRY
BY COUNTER NOPRINT
BY TITLE_VO AS ' '
IF COUNTER LE 5
END

What does the FSEQ contains? I've seen how to create the FSEQ file but what does this file contains to make this technique work?

Another way of doing this type of report is

  
TABLE FILE WHATEVER
PRINT 
 COL1 AS COLUMNA
ON TABLE HOLD AS TABLE1
END
TABLE FILE WHATEVER
PRINT 
 COL2 AS COLUMNA
ON TABLE HOLD AS TABLE2
END
TABLE FILE WHATEVER
PRINT 
 COL3 AS COLUMNA
ON TABLE HOLD AS TABLE3
END
TABLE FILE WHATEVER
PRINT 
 COL4 AS COLUMNA
ON TABLE HOLD AS TABLE4
END
TABLE FILE HOLD1
PRINT 
  COLUMNA
ON TABLE HOLD AS TABLEALL
MORE
 FILE TABLE2
MORE 
 FILE TABLE3
MORE 
 FILE TABLE4
END
TABLE FILE TABLEALL
PRINT
 COLUMNA
END

L_G,

Taking a leaf from Tony's book, and using an alternate master:

  
FILEDEF LGMAS DISK LGDATA.MAS
FILEDEF LGDATA DISK LGDATA.FTM
-RUN
-WRITE LGMAS FILE=LGDATA,SUFFIX=COM
-WRITE LGMAS SEGNAME=SEG1
-WRITE LGMAS FIELD=COL1, ,I9 ,A9 , $
-WRITE LGMAS FIELD=COL2, ,I9 ,A9 , $
-WRITE LGMAS FIELD=COL3, ,I9 ,A9 , $
-WRITE LGMAS FIELD=COL4, ,I9 ,A9 , $
-WRITE LGDATA 1,2,3,4,$
-WRITE LGDATA 5,6,7,8,$
-WRITE LGDATA 9,10,11,12,$
-WRITE LGDATA 13,14,15,16,$
-RUN
DEFINE FILE LGDATA
SP/A1=' ';
END
TABLE FILE LGDATA
PRINT SP COL1 COL2 COL3 COL4
ON TABLE SAVE AS COLS
END
-RUN
FILEDEF MCOLS DISK COLS.MAS
-RUN
-WRITE MCOLS FILE=COLS,SUFFIX=FIX
-WRITE MCOLS SEGNAME=SP
-WRITE MCOLS FIELD=SP, ,A1 ,A1 , $
-WRITE MCOLS SEGNAME=COL, PARENT=SP, OCCURS=VARIABLE
-WRITE MCOLS FIELD=COL, ,I9 ,A9 , $
-RUN
TABLE FILE COLS
PRINT COL
END