Hi all
I would like to know what is the difference in using a Focus DB with SET ALL=ON option and without that option. My master file looks like this:

FILENAME=TEST,SUFFIX=FOC,$
SEGNAME=HEADER,SEGTYPE=S4,$
FIELDNAME=BAC ,,A6 ,$
FIELDNAME=SET ,,A1 ,$
FIELDNAME=SOURCE ,,A5 ,$
FIELDNAME=DA ,,A1 ,$
SEGNAME=BUS,PARENT=HEADER,SEGTYPE=S10,$
FIELDNAME=LTEST ,,A3 ,$
GROUPNAME=CETESTJ ,,A7 ,$
FIELDNAME=TESTCOM ,,A3 ,$
FIELDNAME=TMJOR ,,A4 ,$
FIELDNAME=TRANS ,,A10 ,$
FIELDNAME=OTESTCO ,,A5 ,$
SEGNAME=CAP,PARENT=BUS,SEGTYPE=S1,$
FIELDNAME=CTESTCD ,,A4 ,$
SEGNAME=EXPENSES,PARENT=CAP,SEGTYPE=S2,$
FIELDNAME=ETESTS ,,A8 ,$
FIELDNAME=STEST ,,A8 ,$
SEGNAME=DETAIL,PARENT=EXPENSES,SEGTYPE=S1,$
FIELDNAME=YEAR ,,A6 ,$
FIELDNAME=VALUES ,,D14.2 ,A14 ,$
FIELDNAME=CHANGE ,,D14.2 ,A14 ,$
SEGNAME=ABC,PARENT=HEADER,SEGTYPE=KU,CRFILE=REFFILE,CRKEY=BAC,$
SEGNAME=ABD,PARENT=BUS,SEGTYPE=KU,CRFILE=REFFILE,CRKEY=LTEST,$
SEGNAME=ABE,PARENT=BUS,SEGTYPE=KU,CRFILE=REFFILE,CRKEY=CTESTCD,$

I am in the process of converting Focus DB s to flat files. I am using a TABLE FILE statement with FORMAT ALPHA.
I am getting more records in the output when I keep the option SET ALL=ON.

I checked in the manual, but couldn't get anything relevant to my scenario. Requesting your help in understanding as I am quite new to the Focus.

This message has been edited. Last edited by: <Kathryn Henning>,

From the WebFOCUS Keysheet SET

ALL = option ......... control parent selection with no child records
■ option: OFF ........ [default] never get the parent if no child exists
■ option: ON ......... get parent, except if screening on child
■ option: PASS ....... get parent, even when screening on child

Hi could you please explain this to me in layman terms.

I checked my master file. It appears like this.

FILENAME=TEST,SUFFIX=FOC,$
SEGNAME=HEADER,SEGTYPE=S4,$
FIELDNAME=BAC ,,A6 ,$
FIELDNAME=SET ,,A1 ,$
FIELDNAME=SOURCE ,,A5 ,$
FIELDNAME=DA ,,A1 ,$
SEGNAME=BUS,PARENT=HEADER,SEGTYPE=S10,$
FIELDNAME=LTEST ,,A3 ,$
GROUPNAME=CETESTJ ,,A7 ,$
FIELDNAME=TESTCOM ,,A3 ,$
FIELDNAME=TMJOR ,,A4 ,$
FIELDNAME=TRANS ,,A10 ,$
FIELDNAME=OTESTCO ,,A5 ,$
SEGNAME=CAP,PARENT=BUS,SEGTYPE=S1,$
FIELDNAME=CTESTCD ,,A4 ,$
SEGNAME=EXPENSES,PARENT=CAP,SEGTYPE=S2,$
FIELDNAME=ETESTS ,,A8 ,$
FIELDNAME=STEST ,,A8 ,$
SEGNAME=DETAIL,PARENT=EXPENSES,SEGTYPE=S1,$
FIELDNAME=YEAR ,,A6 ,$
FIELDNAME=VALUES ,,D14.2 ,A14 ,$
FIELDNAME=CHANGE ,,D14.2 ,A14 ,$

There is no cross reference.

I am working on a migration project and Focus is quite new to me.

I get 8661962 records in my output flat file without SET option, but with SET ALL=ON, I am getting 8678418 records. I am really not sure from where I am getting the addition records when there is SET ALL=ON option.

If you or anyone could explain this in more simpler/layman terms, it would be of great help.
Any help on this would be greatly appreciated. Thanks.

For every row in SEGNAME=HEADER is there a row in SEGNAME=BUS?

See if this helps.
Run this with ALL = ON and then run with ALL = OFF

-*SET ALL = OFF
SET ALL = ON
TABLE FILE CAR
PRINT WARR
BY COUNTRY
BY CAR
BY MODEL
END

Thanks for your response Spence. I tried running the code that you provided. But it is giving the following error:
TABLE FILE CAR
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: CAR
BYPASSING TO END OF COMMAND

I am using Focus in Mainframe environment. Do we need to give allocation to any file to have this sample code work? Or do we need to point to any different Focexec library before running this?

The FOCUS db is a hierarchical structure. That is values can exist in a parent segment without existing in a child segment. A complete record through all segment does not have to exist. A child though, has to have a parent.

For example, if there is a BAC value of 'abcdef' which does not have an LTEST value, then the parent does not have a child instance.

Using the default of SET ALL=OFF and you then print BAC and LTEST, then a parent without a child will not be retrieved, i.e. the value of 'abcdef' will not appear in a report if using PRINT BAC LTEST. This is because you are asking for BAC and LTEST. Ergo unless both exist the record will not be retrieved.

Using SET ALL=ON then the same request will retrieve the BAC value of 'abcdef' with LTEST showing as missing. This option tells FOCUS to retrieve parents without children as well as parents with children. So all BAC will be retrieved whether or not they have an LTEST value.

Now lets suppose you want to find all BAC instances that do not have any children of LTEST. Using SET ALL=PASS and using an IF/WHERE condition on LTEST that is impossible to pass, i.e. PRINT BAC IF LTEST EQ 'XXX', the SET ALL=PASS will pass screening on missing LTEST values as if the missing value had PASSed the screening condition, and 'abcdef' will be returned, along with any other BAC with no LTEST, but no BAC with an LTEST child will be retrieved, as LTEST would not pass the screening condition.

Hope this makes sense, can be difficult to understand, and trying it out is the best approach.

Amazing explanation! I got most of it! Thank you so much!

One more query.. Actually I am in the process of converting the Foc DB s to flat files as I need to use them inside another language (SAS). I have converted them to flat files (FORMAT ALPHA) using SET ALL=ON option. Once I convert them to flat files, is there a way by which I can exclude the additional records that are obtained by having SET ALL=ON from the flat file?

I tried excluding the records with missing values by giving all fields (with OR condition), except the ones present in the header segment. But it doesn't seem to give the correct results. Any suggestions..

For instance,

IF LTEST NE "" OR CTESTJ NE ""....CHANGE NE "";

The records returned doesn't match the records that we get using SET ALL=OFF. I need the extra records for some cases , don't require in some other cases and it is essential that I have all of them in same file.

Thanks!

This should work. Maybe you need to check the actual contents of the file.

quote:

For instance,
IF LTEST NE "" OR CTESTJ NE ""....CHANGE NE "";

Not certain what you are trying to accomplish with this -- but that's not valid syntax for an IF clause.