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] JOIN TWO HOLD FILES

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] JOIN TWO HOLD FILES
 Login/Join
 
sladkat
Member
posted
Hi,
We have 2 hold files HOLD1 AND HOLD2.
From the HOLD1 we need to get distinct codes (cod_val).
HOLD2 has following structure
lowval_code
hival_code
cod_catagory
cod_description

So basically we need to get cod_category and cod_description for the distinct values of cod_val from HOLD1
SELECT
COD_CATEGORY ,
COD_DESCRIPTION
FROM HOLD2
WHERE COD_VAL(HOLD1) BETWEEN LOWVAL_CODE AND HIVAL_CODE

Please Reply.
Thanks

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


Supriya
WF 7.7.02/8.0
 
Posts: 18 | Registered: November 16, 2011Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Ideally, your hold files would already have a dummy field containing only a blank. If not, then you will need to go through the extra steps of adding them to your existing hold files. Then you can join the two hold files on the dummy field and apply your WHERE condition. Something like this:

TABLE FILE HOLD1
 SUM COMPUTE BLANK/A1 = ' ';
 BY COD_VAL
 ON TABLE HOLD AS HOLD1X
END
-*
TABLE FILE HOLD2
 SUM
  FST.COD_CATEGORY
  FST.COD_DESCRIPTION
  COMPUTE BLANK/A1 = ' ';
 BY LOWVAL_CODE
 BY HIVAL_CODE
 ON TABLE HOLD AS HOLD2X
END
-*
JOIN CLEAR *
JOIN BLANK IN HOLD1X TO ALL BLANK IN HOLD2X AS J1
-*
TABLE FILE HOLD1X
 PRINT
  HOLD2.COD_CATEGORY
  HOLD2.COD_DESCRIPTION
 BY HOLD1_COD_VAL
 WHERE (HOLD1.COD_VAL FROM LOWVAL_CODE TO HIVAL_CODE);
END

It's possible the JOIN may not work correctly. In that case try changing the HOLD statement for HOLD2x to:

ON TABLE HOLD AS HOLD2X FORMAT FOCUS INDEX BLANK

Finally, if neither of the above produce the desired results, you may need to use a conditional JOIN: Using a Conditional Join.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
sladkat
Member
posted Hide Post
It Worked with ON TABLE HOLD AS HOLD2X FORMAT FOCUS INDEX BLANK.

Thanks a lot Smiler


Supriya
WF 7.7.02/8.0
 
Posts: 18 | Registered: November 16, 2011Report 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] JOIN TWO HOLD FILES

Copyright © 1996-2020 Information Builders