 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point Printing Records from a Primary File
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Printing Records from a Primary File Read-Only TopicGo | Search | Notify | Tools |
| Printing Records from a Primary File | Login/Join |
| Member |
I am currently joining FileA to the Customer File to pick up the customer name. I am doing an outer join. There can be multiple customer records per customer (i.e. a customer in multiple companies). So, within the TABLE PRINT command, I am selecting one company only on the customer file, with a WHERE clause. In one particular case, the customer record doesn't exist at all and because of the WHERE clause, transactions from File A are getting omitted. How can I pick up all the transactions and one customer record only per transaction?? Thank you. | ||
|
| Platinum Member |
Jane, Prior to your join, do you issue the SET ALL=ON command? If not, try that. Also, it would be helpful to know what version of WF you are using. Kevin WF 7.6.10 / WIN-AIX | |||
|
| Gold member |
Just noticed your email - I'm in a hurry - try: SET ALL = ON TABLE FILE.... WebFOCUS 7.6.11, WINDOWS, HTML, PDF, EXCEL | |||
|
| Member |
Thank you. I have SET ALL = PASS, just prior to the JOIN statement. I am running version 533 of Web Focus. | |||
|
| Member |
I thought I'd just add, I've also tried SET MULTIPATH = SIMPLE, but none of it seems to make much difference - the report still omits the transactions where the customer doesn't exist on the customer file! | |||
|
| Platinum Member |
Jane, Please post an example of your JOIN syntax. The other item that affects this is th ALL syntax in the JOIN statement itself. JOIN fielda in filea to ALL fielda in fileb AS.. Kevin WF 7.6.10 / WIN-AIX | |||
|
| Member |
My JOIN statement is as follows: SET MULTIPATH = SIMPLE SET ALL = PASS JOIN F55OR.PNM_CUSTOMER_PARENT_NUMBER IN F55OR TO F5701.CUS_CUSTOMER_NUMBER IN F5701 AS JO END TABLEF FILE F55OR PNM_CUSTOMER_PARENT_NUMBER F5701.ABR_ALPHA_NAME AS 'Parent Name' WHERE F5701.COP_COMPANY EQ '000'; END | |||
|
| Member |
I tried adding the ALL in my join statement, but unfortunately it still didn't select transactions with a missing customer number on the customer file! | |||
|
| Expert |
Jane, What OS and what version of WF are you on? Also, what is your datasource? MS SQL, DB2, Oracle etc. There are different best answers depending upon this info. T
| |||||||||
|
| Member |
I am using OS XP Professional; Web Focus version 533 and accessing files on an AS/400. | |||
|
| Expert |
Hi Jane, What sort of files on AS400? If they are DB2 or accesible via SQL then I would always suggest using SQL passthru to provide the recordset for you rather than trying to get WebFOCUS to do it for you. It can be more efficient, let alone easier in some cases. I don't think that Version 5.3.3 has the new function within the report painter to set the type of join, otherwise an alternative would be to set a left outer join using that. So, unless you can use SQL passthru, you are stuck with trying to achieve it in WebFOCUS. Some example code. Note that the HOLD FORMAT FOCUS file is a subset of the CAR table so that we can mimic your situation using the sample database CAR. If you copy this code and run it, you should see that it gives you your left outer join result. SET ASNAMES = ON
SET ALL = PASS
TABLE FILE CAR
SUM RETAIL_COST AS RCOST2
DEALER_COST AS DCOST2
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','ITALY')
ON TABLE HOLD AS MYCAR2 FORMAT FOCUS INDEX COUNTRY
END
-RUN
JOIN CLEAR *
JOIN COUNTRY IN CAR TO ALL COUNTRY IN MYCAR2 AS J1.
END
-RUN
TABLE FILE CAR
SUM RCOST
RCOST2
DCOST
DCOST2
BY COUNTRY
END
-RUN
Good luck T
| |||||||||
|
| Member |
Thankyou. I was coming to the conclusion that I would have to summarise my customer file first and then not worry about the WHERE clause in the TABLE PRINT statement! Thanks to everyone for all your help | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Printing Records from a Primary File
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Printing Records from a Primary FileCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |