Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Parent Child Relationship

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Parent Child Relationship
 Login/Join
 
Noel
Silver Member
posted
I need to write a report to extract the last component item no. of a parent item number.

Here is an example of our data.

Parent Item Number Component Item Number
00876645203 86028881
86028881 09845997
86028881 86029391

When the user selects parent item number 00876645203 then my output must display 86029391.

Thanks in advance for the help.

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


WebFocus 762 AS400 / DB2
 
Posts: 34 | Location: Winnipeg, MB | Registered: January 23, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
Noel,

You have to use a recursive join to achieve this.

The following code gives you the result you're looking for. I'm not 100% sure this will work in the real world.

I create a master and datafile to simulate the data you provide. I have to make a FOCUS DB because recursive joins do not work on flat files. If you're reading a DBMS table, you do not have to create a FOCUS DB.

The recursive join uses TAG to uniquely qualify the field names in the recursive join.

-SET &ECHO=ON;

SET HOLDLIST=PRINTONLY
-RUN

-*-- Create the master for TEST6 -----------------
FILEDEF DATAMAST DISK TEST6.MAS
-RUN

-WRITE DATAMAST FILE=TEST6, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S2, $
-WRITE DATAMAST FIELD=PITEM,  ALIAS=PITEM,  USAGE=A11,    ACTUAL=A11, $
-WRITE DATAMAST FIELD=CITEM,  ALIAS=CITEM,  USAGE=A11,    ACTUAL=A11, $

-*-- Create the data file for TEST6 --------------
FILEDEF TEST6 DISK TEST6.FTM
-RUN

-*           1234567890A1234567890A
-WRITE TEST6 00876645203   86028881
-WRITE TEST6    86028881   09845997
-WRITE TEST6    86028881   86029391

-*-- Create a FOCUS database ---------------------
TABLE FILE TEST6
PRINT
PITEM NOPRINT
BY PITEM
BY CITEM
ON TABLE HOLD AS HTEST6 FORMAT FOCUS INDEX PITEM CITEM
END
-RUN

-*-- Create the report ---------------------------
SET ALL=ON
JOIN CITEM IN HTEST6 TO ALL PITEM IN HTEST6 TAG T1 AS J1
-RUN

?FF HTEST6
-RUN

TABLE FILE HTEST6
SUM
COMPUTE DUMMY1/A1 = ''; NOPRINT

BY PITEM
BY HIGHEST 1 T1.CITEM

WHERE T1.CITEM IS NOT MISSING
END

Recursive joins are described here: Describing Data With WebFOCUS Language > Describing a Group of Fields > Recursive Relationships and here: Creating Reports With WebFOCUS Language > Joining Data Sources > Types of Joins > Recursive Joined Structures.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
Seems a bit elaborate to me.

If the data is in the correct sort order you simply need

TABLEF FILE TEST6
SUM
LST.CITEM
BY PITEM
END
 
Report This Post
Noel
Silver Member
posted Hide Post
Hi Francis,

I just want to thank you for the inputs. It works!

Noel


WebFocus 762 AS400 / DB2
 
Posts: 34 | Location: Winnipeg, MB | Registered: January 23, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
Noel, happy to know I helped a little.

Merci.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Parent Child Relationship

Copyright © 1996-2020 Information Builders