Focal Point
SET ALL=PASS

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8861045642

September 12, 2007, 02:50 PM
Mark1
SET ALL=PASS
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
September 12, 2007, 02:58 PM
Leah
'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
September 12, 2007, 03:13 PM
Mark1
Is there any way to get around this so that all secondary data shows?


Windows version 768
September 13, 2007, 12:22 AM
susannah
MATCH !




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
September 13, 2007, 07:16 AM
Jessica Bottone
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
September 13, 2007, 07:45 AM
ET
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
September 13, 2007, 03:52 PM
FrankDutch
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

September 13, 2007, 05:02 PM
Mark1
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
September 13, 2007, 06:15 PM
susannah
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
September 14, 2007, 08:09 AM
Mark1
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
September 14, 2007, 12:24 PM
ET
Mark,

Glad that using universal concatenation worked for you.

et


FOCUS 7.6 MVS PDF,HTML,EXCEL