Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] single row into multiple rows
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] single row into multiple rows
 Login/Join
 
Member
posted
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>,


WebFOCUS 7.6.2
Windows
Excel,PDF
 
Posts: 1 | Registered: March 03, 2010Reply With QuoteReport This Post
Master
posted Hide Post
Look up OCCURS.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Also, think about what you will do with city names with spaces, for examaple:

Chicago Heights
East St. Louis


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 147 | Registered: October 19, 2010Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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 Smiler

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5637 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
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




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1941 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1941 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Master
posted Hide Post
quote:
EX -LINES {n} EDAPUT {File Type},{App/}{File Name},{Create Type},{Create Location}

Thanks for sharing Daniel.
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] single row into multiple rows

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.