Focal Point
How to join Host to same CrossReference 3-times?
February 22, 2008, 02:46 PM
cburttHow to join Host to same CrossReference 3-times?
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.
February 22, 2008, 03:02 PM
Darin LeeHow 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
February 22, 2008, 07:52 PM
N.SelphYou 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)
February 23, 2008, 07:38 AM
FrankDutchWhat 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 |
February 25, 2008, 09:40 AM
PBrightwellN.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
February 25, 2008, 11:57 AM
Darin LeeI 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
February 25, 2008, 03:44 PM
RSquaredYou 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
February 25, 2008, 06:11 PM
Darin LeeIf 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
February 26, 2008, 12:47 PM
cburttThanks, 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.