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     How to join Host to same CrossReference 3-times?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How to join Host to same CrossReference 3-times?
 Login/Join
 
Platinum Member
posted
The Requirement:
I must 'decode' three addressIDs in a host table with the same CrossReference table.
This means joining each host row to 3 different rows in the same JOINed table.
The host contains a CustomerNumber and the EmployeeIDs of two salespersons servicing that customer.
There is a single 'Address Book' table that identifies all Customers, Vendors, Employees, etc.
The report must show both the IDs and the respective alphabetic names of the customer and the two salespersons.

DevStudio is being used to code the FocExec.

The Issue:
The JOINS are described with DevStudio's JOIN tool, but "Run"ing the tool produces this error, twice:
(FOC1831) TAGNAME OR FILENAME IS DUPLICATED IN THIS JOIN/COMBINE: @0000000
If the JOIN errors are overlooked, the FocExec runs but all three decode operations show same the same name. Clearly, only one JOIN worked.

I thought that in the PRINT specification one uses the JOIN's 'tag' or 'J#' to specify the JOINed segment that is to supply the displayed data.

How do I correctly specify the JOINs so that the list of columns that I can drag-n-drop onto the report canvas triples in size with the addition of three qualified instances of the AddressBook?

Here's my code, aligned for readability:
(When run, it fails with FOC1831.)

  

-* =============================================================================

-* MFD for "Commissions" (Host) table (Uninvolved fields removed).
-* Alias changed to identify data.
FILENAME=_F5542004, SUFFIX=SQLMSS  , $
  SEGMENT=_F5542004, SEGTYPE=S0, $
    FIELDNAME=CCCO, ALIAS=Company, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CCMCU, ALIAS=Order_Line_Item_Business_Unit, USAGE=A12, ACTUAL=A12, $
    FIELDNAME=CCAN8, ALIAS=Customer_ID, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=CCCMC1, ALIAS=Sales_Person_1_ID, USAGE=P19, ACTUAL=P10, MISSING=ON, $
    FIELDNAME=CCCMC2, ALIAS=Sales_Person_2_ID, USAGE=P19, ACTUAL=P10, MISSING=ON, $
    FIELDNAME=CCEMCU, ALIAS=Order_Header_Business_Unit, USAGE=A12, ACTUAL=A12, MISSING=ON, $
    FIELDNAME=SOURCEID, ALIAS=Source_ID, USAGE=I11, ACTUAL=I4, $

-* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

-* MFD for "AddressBook" (Cross Reference) table (Uninvolved fields removed).
-* ADDRESS_NUMBER is indexed in MS/SQL RDBMS.
FILENAME=_F0101, SUFFIX=SQLMSS  , $
  SEGMENT=_F0101, SEGTYPE=S0, $
    FIELDNAME=ADDRESS_NUMBER, ALIAS=Address_Number, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=ALPHA_NAME, ALIAS=Alpha_Name, USAGE=A40V, ACTUAL=A40V, MISSING=ON, $
    FIELDNAME=SOURCE_ID, ALIAS=Source_ID, USAGE=I11, ACTUAL=I4, $

-* =============================================================================

-* FocExec generated by DevStudio:

-* JOINDESC J0 Joins CommSlsPer1ID to AdBkAddress# to get AdBkSlsPer1Name
-* JOINDESC J13 Joins CommSlsPer2ID to AdBkAddress# to get AdBkSlsPer2Name
-* JOINDESC J2 Joins CommCustomrID to AdBkAddress# to get AdBkCustomerName
JOIN
 SALESPERSON_01 WITH _F5542004._F5542004.CCCO IN _F5542004 TAG CSP1ID TO UNIQUE
 _F0101._F0101.ADDRESS_NUMBER IN _F0101 TAG ABSP1N AS J0
 END
JOIN
 SALESPERSON_02 WITH CSP1ID._F5542004.CCAN8 IN _F5542004 TAG CSP2ID TO UNIQUE
 _F0101._F0101.ADDRESS_NUMBER IN _F0101 TAG ABSP2N AS J13
 END
JOIN
 CSP1ID._F5542004.CCAN8 IN _F5542004 TAG CCUST TO UNIQUE
 _F0101._F0101.ADDRESS_NUMBER IN _F0101 TAG ABCUST AS J1
 END
 -* DEFINEs convert CommSlsPersID's to format used by AdBk.
DEFINE FILE _F5542004
  CUST_ID/A11=EDIT(CCAN8);
  SALESPERSON_01/I11=CCCMC1;
  SALESPERSON_02/I11=CCCMC2;
END
TABLE FILE _F5542004
  PRINT
    '_F5542004._F5542004.CCCO'    AS 'Company,_F5542004.CCCO'
    '_F5542004._F5542004.CUST_ID' AS 'Cust #,_F5542004.CUST_ID'
    ALPHA_NAME                    AS 'Cust Name,._F0101.ALPHA_NAME'
    '_F5542004._F5542004.CCEMCU'  AS 'Hdr RBU,_F5542004.CCEMCU'
     _F5542004.SALESPERSON_01     AS 'SlsPrsn1 #'
    ALPHA_NAME                    AS 'Slsprsn1 Name,._F0101.ALPHA_NAME'
     _F5542004.SALESPERSON_02     AS 'SlsPrsn2 #'
    ALPHA_NAME                    AS 'Slsprsn2 Name,._F0101.ALPHA_NAME'
    '_F5542004._F5542004.SOURCEID'
  BY '_F5542004._F5542004.CCCO'    NOPRINT
  BY '_F5542004._F5542004.CUST_ID' NOPRINT
  BY '_F5542004._F5542004.CCEMCU'  NOPRINT
  WHERE _F5542004.SALESPERSON_01 NE _F5542004.SALESPERSON_02
  WHERE _F5542004._F5542004.SOURCEID EQ '11';
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
$
ENDSTYLE
END


WIN/2K running WF 7.6.4
Development via DevStudio 7.6.4, MRE, TextEditor.
Data is Oracle, MS-SQL.
 
Posts: 154 | Location: NY | Registered: October 27, 2005Report This Post
Virtuoso
posted Hide Post
How about

PRINT
CCCO AS 'Company'
CUST_ID AS 'Cust #'
J0ALPHA_NAME AS 'Cust Name'
CCEMCU AS 'Hdr RBU'
SALESPERSON_01 AS 'SlsPrsn1 #'
J13ALPHA_NAME AS 'Slsprsn1 Name'
SALESPERSON_02 AS 'SlsPrsn2 #'
J1ALPHA_NAME AS 'Slsprsn2 Name'
SOURCEID

You probably also want to eliminate the where on defined fields and just say
WHERE CCCMC1 NE CCCMC2;


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Guru
posted Hide Post
You have tagged the host table 3 different things:

quote:

_F5542004 TAG CSP2ID
_F5542004 TAG CSP1ID
_F5542004 TAG CCUST


It can only be one of them. It is _F0101 that is used 3 times, and therefore needs to be tagged differently each time.


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
Virtuoso
posted Hide Post
What I would do in this case is create 3 different masters that all point to the same AddressBook SQL table.

You have one already.

SALEPER1.MAS

FILENAME=SALEPER1, SUFFIX=SQLMSS  , $
  SEGMENT=SALEPER1, SEGTYPE=S0, $
    FIELDNAME=CCCMC1, ALIAS=Address_Number, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=CCCMC1NAME, ALIAS=Alpha_Name, USAGE=A40V, ACTUAL=A40V, MISSING=ON, $
    FIELDNAME=SOURCE_ID, ALIAS=Source_ID, USAGE=I11, ACTUAL=I4, $

SALEPER2.MAS

FILENAME=SALEPER2, SUFFIX=SQLMSS  , $
  SEGMENT=SALEPER2, SEGTYPE=S0, $
    FIELDNAME=CCCMC2, ALIAS=Address_Number, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=CCCMC2NAME, ALIAS=Alpha_Name, USAGE=A40V, ACTUAL=A40V, MISSING=ON, $
    FIELDNAME=SOURCE_ID, ALIAS=Source_ID, USAGE=I11, ACTUAL=I4, $


Remember you have to create 2 other ACX files too.

Now you have a master for all the 3 needed names (client and two accountmanagers)

Maybe you can reformat the 2 keyfields to the needed format.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
N.Selph pointed out that the host tag should be the same in all 3 joins, but the tags for the joined field have to be used in the print (TAG.ALPHA_NAME) or you will always get the first occurance. Also, why do you have single quotes around your field names in your print statement ('_F5542004._F5542004.CCCO')? Why not just CCCO?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
I posted the code I would use, but didn't get in my version of the join. What you are using would work but can be simplified a lot and still works with the sample code:
JOIN SALESPERSON_01 WITH CCCO IN _F5542004 TO ADDRESS_NUMBER IN _F0101 AS J0
 END
JOIN
 SALESPERSON_02 WITH CCAN8 IN _F5542004 TO  ADDRESS_NUMBER IN _F0101 AS J13
 END
JOIN
 CCAN8 IN _F5542004 TO ADDRESS_NUMBER IN _F0101 AS J1
 END


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Guru
posted Hide Post
You have to tag each name with the Join name
ie. j0.ALPHA_NAME j13.ALPHA_NAME etc., so that Webfocus will know which name to use.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Virtuoso
posted Hide Post
If you look at my sample code, you will see that I have that in there, but you can't use the dot or WF will assume it's a file or segment name prefix:
J0ALPHA_NAME
J13ALPHA_NAME
etc.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
Thanks, all, for your help. I was able to get it working perfectly!

By base problem was that I 'tagged' each instance of the Host in my JOINS differently. I interpreted them as different because they included a different target field.

My second problem was that I fell into the same error as RSquared did before Darrin Lee corrected him: I put a "." between the "Jx" qualifier and the target field in the PRINT list.

Properly structured, the CrossReference table's TAG values show in the tool's list of columns as a qualifier and identifies of which JOINED segment the column comes from while the Host table's TAG value identifies columns sourced from that table.

PBrightwell commented on the apostrophies surrounding the fully qualified column references. Well, that's what DevStudio's report painter tool does when it saves what's "inside" the Report "box". Did you ever use TextView after saving a Physical View?

Finally, FrankDutch, I don't see the advantage of separate MFD's. I need all three alphabetic names to print in the same edition of the report.

Again, Thanks Everyone.


WIN/2K running WF 7.6.4
Development via DevStudio 7.6.4, MRE, TextEditor.
Data is Oracle, MS-SQL.
 
Posts: 154 | Location: NY | Registered: October 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     How to join Host to same CrossReference 3-times?

Copyright © 1996-2020 Information Builders