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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to join two hold files

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to join two hold files
 Login/Join
 
Member
posted
I am trying to join two hold files for further processing and have not gotten it to work yet. A simple example, basing both hold files on the same source just for demonstration:

-* Hold file 1
TABLE FILE CUSTOMERS
BY CUSTOMER_ID
BY LAST_NAME
ON TABLE HOLD AS HOLD_NAMES
END

-* Hold file 2
TABLE FILE CUSTOMERS
BY CUSTOMER_ID
BY COUNTRY
ON TABLE HOLD AS HOLD_COUNTRIES
END

-* Join hold files
JOIN CUSTOMER_ID IN HOLD_NAMES
TO CUSTOMER_ID IN HOLD_COUNTRIES AS J1
END

-* Output
TABLE FILE HOLD_NAMES
BY CUSTOMER_ID
BY J1.COUNTRY
ON TABLE PCHOLD FORMAT HTML
END


This generates no output. Both hold files individually look fine, having the expected content.

Suggestions in similar posts are to use the FOCUS format for the hold files, INDEX them, and use fully qualified fieldnames. Neither of those suggestions worked for me. I also did not find the documentation to be of much help. Any ideas?

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


WebFOCUS 7.7.03
Windows 7 64-bit
 
Posts: 17 | Registered: February 23, 2016Report This Post
Virtuoso
posted Hide Post
Check the output (for HTML; view source). If I apply your example to the CAR database I get an error about J1.MODEL:
APP PREPENDPATH IBISAMP

-* Hold file 1
TABLE FILE CAR
BY COUNTRY
BY CAR
ON TABLE HOLD AS HOLD_NAMES
END

-* Hold file 2
TABLE FILE CAR
BY COUNTRY
BY MODEL
ON TABLE HOLD AS HOLD_COUNTRIES
END

-* Join hold files
JOIN COUNTRY IN HOLD_NAMES
TO COUNTRY IN HOLD_COUNTRIES AS J1
END

-* Output
TABLE FILE HOLD_NAMES
BY COUNTRY
BY J1.MODEL
ON TABLE PCHOLD FORMAT HTML
END


 (FOC003) THE FIELDNAME IS NOT RECOGNIZED: J1.MODEL
 BYPASSING TO END OF COMMAND
 (FOC009) INCOMPLETE REQUEST STATEMENT


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Member
posted Hide Post
I do not get that error. The only messages in the HTML source are
 0 NUMBER OF RECORDS IN TABLE=        6  LINES=      6
 0 NUMBER OF RECORDS IN TABLE=        6  LINES=      6


Same when I run your example using the CAR table:
 0 NUMBER OF RECORDS IN TABLE=       10  LINES=     10
 0 NUMBER OF RECORDS IN TABLE=       18  LINES=     18


WebFOCUS 7.7.03
Windows 7 64-bit
 
Posts: 17 | Registered: February 23, 2016Report This Post
Master
posted Hide Post
If I take out the "J1" it seems to work:

-* Output
TABLE FILE HOLD_NAMES
BY COUNTRY
BY MODEL
ON TABLE PCHOLD FORMAT HTML
END


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Member
posted Hide Post
Yes, when leaving out the aliases it works! Thank you.

I do not quite understand that behavior but the problem is solved.


WebFOCUS 7.7.03
Windows 7 64-bit
 
Posts: 17 | Registered: February 23, 2016Report This Post
Master
posted Hide Post
Just a guess here, but in the car file example MODEL only exists in one of the two hold files being joined.

Joining those two hold files together still means there is only one MODEL field, so there really is no need to prefix it with anything.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Master
posted Hide Post
The error on J1 is because that is the definition for the join, not the table. What you would want to do to specify the table with a short name is use TAG

 JOIN COUNTRY IN HOLD_NAMES
TO COUNTRY IN HOLD_COUNTRIES TAG J1 AS J1 


Please note that the tag does not need to be the same as the Join short name. I just made them the same in this case because that's how it is being referenced in the procedure


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
DomR,

Our posts seem to have gotten crossed. Please see my comment above about tag names. The AS refers to the JOIN as a whole (So both tables). WebFOCUS allows you to do things like clear a particular join. In those cases you would do something like

 JOIN CLEAR J1 


TAG allows you to create your prefix. Here is what the help file says about it.

quote:
Adding a Tag Name
How to:

Add a Tag Name


Tag names provide a way of making identical field names a single instance. They function as aliases for the file names in the Join. Tag names are useful when you join a file to itself because in such a Join all field names, segment names, and file names are the same in the host and cross-referenced files. For more information, see Joining a Data Source to Itself: The Recursive Join. Tag names are also useful if a file is used as a target more than once in the join structure.

A procedure can use the tag name of a file as a qualifier for field names and aliases in the file.

If you create a report using a recursive Join without tag names, the field names in the cross-referenced file are prefixes with the first four characters of the Join name.


--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


xx
Procedure: How to Add a Tag Name
To add a tag name to a file in the Join:

Double-click the link between the tables. The Join Properties dialog box appears. The Join Properties dialog box also appears if you right-click a selected link or select Properties from the View menu. A thick line appears when a join is selected.
Enter names in the Host Tag and Cross Tag name fields and click OK.
Note:

The following words cannot be used as a tag name: ALL, AND, AS, IN, JOIN, TAG, TO, WITH.
Tag names can be up to eight characters in length.


--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Example: Impact of Tag Names on Join Syntax
The following example illustrates the effect of tag names on the Join syntax:

JOIN
JOBFILE.JOBSEG.JOBCODE IN jobfile TAG tag name Job TO ALL
JOBFILE.JOBSEG.JOBCODE
IN jobfile TAG tag name Jobc AS J4
END


If you add the tag name FILE1 to the host file, and the tag name FILE2 to the cross-referenced file, the following Join is created:

Join

A report can refer to the field names in the host file with the qualifier FILE1 and in the cross-referenced file with the qualifier FILE2, for example, FILE1.JOBSTART and FILE2.JOBSTART. For additional information, see How to View Join Syntax.

Tag names are not supported in Report Painter. You can use qualified field names in Report Painter to differentiate fields and identify the table from which you want to retrieve data.



Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
Thanks, Eric. I had forgotten about "TAG". My table joins are rather simplistic.

What bothers me is the order of the syntax. It makes more sense in my mind to have it like this:

JOIN COUNTRY IN HOLD_NAMES
  TO COUNTRY IN HOLD_COUNTRIES AS J1 TAG 'J1' 

Since TAG is optional, having it at the end with the TAG name quoted explains more about what is going on than the correct syntax. The quotes indicate a name selected by the programmer, and having it at the end of the statement hints that it is optional. That's my two cents worth, anyway.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Member
posted Hide Post
Thanks for your explanations. This clarifies what is going on and how to use AS and TAG properly.


WebFOCUS 7.7.03
Windows 7 64-bit
 
Posts: 17 | Registered: February 23, 2016Report This Post
Virtuoso
posted Hide Post
You can have TAGs for both sides of the JOIN; that's why they have to go before the AS statement (which is an alias for the entire JOIN).

I use sets of TAGs all the time, as it makes code much better readable than having to repeat (capitalized) TABLE.SEGMENT names everywhere.

It also refers to the correct columns just as accurately as TABLE.SEGMENT, which can be important if multiple masters in your JOIN contain a field with the same name that you need to JOIN on.

Or when you collect aggregated fields from a lot of tables into multiple stages with HOLD-files and want some hint of where those columns originated from...


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report 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] How to join two hold files

Copyright © 1996-2020 Information Builders