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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SET ALL=PASS
 Login/Join
 
Platinum Member
posted
I have SET ALL = PASS at the beginning of my fex. Towards the bottom I try to join a list of 8 IDs back to my main master file. For some reason, my fex eliminates those IDs in the master file that are not in the list of 8. How can I prevent this from happening? I want the finished masterfile to contain all IDs, not just the 8 that are in the short list. I thought SET ALL = PASS prevented this from happening.


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Virtuoso
posted Hide Post
'SET ALL = PASS' says give me all in the primary file whether or not in the secondary file plus some criteria issues. So if you are making the 8 id file the primary, then that's all you'll get.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
Is there any way to get around this so that all secondary data shows?


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Expert
posted Hide Post
MATCH !




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
You could try turning your join around - list your main master file first and your list of 8 second. However, from your post, it sounds like the ID's you have in your list of 8 are not in the main master file at all. In that case, a join will not work regardless of which file is listed first and Susannah is correct - MATCH is your only option. JOIN is for linking up 'like' records between a 'parent' and a 'child' file. If you have records in the 'parent' file that are not in the 'child' file, then SET ALL = PASS will make sure do not lose them. One thing to note, for 'parents' without 'children', the values in all their associated 'child' fields will be null, because they don't have any 'children'. So if you do any filtering against fields in the 'child' file, you will still lose your parents even with SET ALL = PASS unless you include nulls in the filtering.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Platinum Member
posted Hide Post
It appears that a merge of the IDs on both files is desired. Match will work but if the files are really large, I have found that universal contcatenation (MORE) is usually faster.

TABLE FILE FILE1
PRINT ID
ON TABLE HOLD AS HOLD1
MORE
FILE FILE2
END

Good luck


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
Virtuoso
posted Hide Post
ET is wrong...
a concatenation with MORE would only work if the fields you select in the first selection are technical the same (size, number of fields).

The hold files you create should be of format focus and the join field (ID) should be indexed.

So:
TABLE FILE XXX
PRINT
NAME
ADRES
BY ID
ON TABLE FILE HOLD FORMAT FOCUS INDEX ID
END


Now this hold file can be the child file and should be put in a left outer join.




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
Platinum Member
posted Hide Post
I found a workaround. I added the other IDs from the main Master file to the list of 8 using universal concatenation, set up dummy values in the fields and then joined back to the master.

Thanks for all of your help!

While we're here... another question. If you tell WF to print a field that doesn't exist, is there a way to bypass the error message and let WF print out all fields that do exist?


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Expert
posted Hide Post
Do you mean you want a work around for:
TABLE FILE CAR
PRINT TRUCK
END
..which would produce an choking error.
yikes.
if i had to, i would do my join first, then
CHECK FILE mastername HOLD
then
TABLE FILE HOLD
and read the field that contains all the fieldnames for that joined entity, producing some nice little list.
Then make sure your report uses only the fieldnames in that list.
If i had to..




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
Yeah, something like that. This fex will run on a schedule every day. I have an ACROSS in my fex that turns measurements into fields. Some days, some of those measurements will be there, some days they won't. So, after that, when I want to print out specific measurements, if one or two are missing, I still want the others to print out.


Windows version 768
 
Posts: 215 | Registered: March 16, 2006Report This Post
Platinum Member
posted Hide Post
Mark,

Glad that using universal concatenation worked for you.

et


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders