Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SET ALL=ON Query
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] SET ALL=ON Query
 Login/Join
 
Member
posted
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>,


WebFOCUS 7.6.8
Windows
Excel
 
Posts: 6 | Registered: June 16, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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.


WebFOCUS 7.6.8
Windows
Excel
 
Posts: 6 | Registered: June 16, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
For every row in SEGNAME=HEADER is there a row in SEGNAME=BUS?


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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?


WebFOCUS 7.6.8
Windows
Excel
 
Posts: 6 | Registered: June 16, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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!


WebFOCUS 7.6.8
Windows
Excel
 
Posts: 6 | Registered: June 16, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This should work. Maybe you need to check the actual contents of the file.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SET ALL=ON Query

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.