Source table values are -

Dept_ID City1 City2 City3 City4 City5
001 Chicago Lisle Naperville Detroit Troy
002 Detroit Southfield Kalamazoo vegas canton

I would like to load this data into my target HOLD file as

Dept_ID City
001 Chicago
001 Lisle
001 Naperville
001 detroit
001 troy
002 Detroit
002 Southfield
002 Kalamazoo
002 vegas
002 canton

How do I explode each single row in source into multiple rows in target?

This message has been edited. Last edited by: <Kathryn Henning>,

Look up OCCURS.

Also, think about what you will do with city names with spaces, for examaple:

Chicago Heights
East St. Louis

Here is an example using CROSS JOIN and GETTOK.

APP PATH IBISAMP
-RUN
DEFINE FILE CAR
ABC/A100= CAR || ',' || MODEL || ',' ||  BODYTYPE;
END
TABLE FILE CAR
BY ABC
BY COUNTRY 
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS HLD_1 FORMAT FOCUS
END

DEFINE FILE CAR
CNTR/I2=CNTR+1;
END
TABLE FILE CAR
PRINT
CNTR
COUNTRY
ON TABLE HOLD
END

TABLE FILE HOLD
PRINT
CNTR
WHERE CNTR LE 3;
ON TABLE HOLD AS HLD_2 FORMAT FOCUS
END
SQL 
SELECT A.COUNTRY,A.ABC, B.CNTR FROM HLD_1 A, HLD_2 B
;
TABLE
ON TABLE HOLD AS HLD_F
END

DEFINE FILE HLD_F
ABC_N/A30=GETTOK(ABC, 100, CNTR, ',', 30, ABC_N);
END
TABLE FILE HLD_F
BY COUNTRY
BY ABC NOPRINT
BY CNTR NOPRINT
BY ABC_N
END

Hope this helps.

Thanks,
Ram

Check out this post - link - and don't forget to do a search for your question first, it may already have been asked and answered

T

here's a really simple way
first with the CAR file
FILEDEF HOUT DISK HOUT.FTM (APPEND
SET ASNAMES = ON
TABLE FILE CAR
PRINT CAR MODEL
IF COUNTRY IS 'ENGLAND'
ON TABLE HOLD AS HOUT
RUN
PRINT CAR MODEL
IF COUNTRY IS 'ITALY'
ON TABLE HOLD AS HOUT
RUN
PRINT CAR MODEL
IF COUNTRY IS 'W GERMANY'
ON TABLE HOLD AS HOUT
END
-RUN
TABLE FILE HOUT
PRINT *
END
...
then with yours
FILEDEF HOUT DISK HOUT.FTM (APPEND

SET ASNAMES = ON
TABLE FILE whatever
PRINT DEPT_ID City1 AS CITY
ON TABLE HOLD AS HOUT
RUN
PRINT DEPT_ID City2 AS CITY
ON TABLE HOLD AS HOUT
RUN
PRINT DEPT_ID City3 AS CITY
ON TABLE HOLD AS HOUT
...lather rinse repeat
END
-RUN
TABLE FILE HOUT
PRINT CITY BY DEPTID
END

Srini,
First, WELCOME!
Enjoy the Forum, the contributors and, above all, WebFOCUS.

As for you question, George provided a hint.

Assumption 1:
You are extracting your data from a file described by a MASTER where the fields are:

  
Name        Format
DEPTID      I3
CITY1       A20
CITY2       A20
CITY3       A20
CITY4       A20
CITY5       A20

Assumption 2:
You extracted as follows:

  
TABLE FILE ...
PRINT CITY1 CITY2 CITY3 CITY4 CITY5
BY DEPTID
ON TABLE HOLD AS EXT FORMAT ALPHA
END

And now the trick:
Build an alternate master for the EXT file:

  
EX -LINES 6 EDAPUT MASTER,EXT,C,MEM
FILENAME=EXT, SUFFIX=FIX
 SEGNAME=EXT, SEGTYPE=S0
  FIELDNAME=DEPTID, ALIAS=DEPTID, FORMAT=I3, ACTUAL=A3,$
 SEGNAME=CITIES, PARENT=EXT, OCCURS=VARIABLE
  FIELDNAME=CITY, ALIAS=CITY, FORMAT=A20, ACTUAL=A20,$
-RUN
TABLE FILE EXT
PRINT CITY
BY DEPTID
END

Good luck!

Srini,

I'm afraid I was a bit elliptic: I should explain the "EX -LINES".
This is an internal method for creating a file, either on disk or in memory. Very useful when it is necessary to dynamically create a MASTER file within a procedure.
Here is some documentation:

  
The EDAPUT command syntax is:

EX -LINES {n} EDAPUT {File Type},{App/}{File Name},{Create Type},{Create Location}


n                       : Number of lines including EDAPUT Line
File Type               : Type of File (e.g. MASTER, FOCEXEC, ACCESS, etc )
App/                    : Optionally Specify the APP directory
File Name               : Name of the File without the extension
Create Type             : Type of Create (CV=Create Variable, C=Create Fixed, A=Append to file)
Create Location         : File Location (FILE=Write to Current Location, MEM=Write to Memory only)

Make good use of it.

quote:

EX -LINES {n} EDAPUT {File Type},{App/}{File Name},{Create Type},{Create Location}

Thanks for sharing Daniel.