I understand SET ALL = ON/OFF/PASS. We currently have SET ALL = OFF (default) set in our profile.

Most reports begin with an SQL Passthru and so most JOINs are done there. Non SQL programmers that didn't understand the SET ALL command started using MATCH to 'join' files and extract data. It has its place, but is also inefficient. We are an Oracle shop.

WebFOCUS programmers then need to SET ALL = ON in the FOCEXEC to get all the parent records.

My question is: What are the ramifications of changing the default to SET ALL =ON in the profile, so that 1. it doesn't need to be set in each FOCEXEC and 2. programmers wouldn't be 'losing' records?

My thought is negligable. Worst case those reports that don't specifically SET ALL=ON would get more rows, parent records. That may be a good thing.

Second, is SET ALL = PASS really only for FOCUS files? I saw one post that said, and had a link to a help file, that it was not for DB2 tables. What about Oracle tables?

Thanks,
Rick

This message has been edited. Last edited by: Rick Man,

SET ALL is of course only for focus managed joins. It has no bearing on sql and requests processed in it's entirety by the host (r)dbms. SET ALL=PASS has meaning for all kinds of databases, provided focus does manage the join.
You could leave the SET ALL=OFF in the profile. If you need all parent records, you and your programmers could either specify SET ALL=ON or use the JOIN LEFT_OUTER syntax. Both have the same results.
Leaving it OFF ensures that the ALL parameter is used where it is really needed, and not 'accidentally' produce the right results.

Well you learn something new everyday. FOCUS managed, like a DEFINE based JOIN?

So the ALL parameter isn't needed if the RDBMS is doing the JOIN, again only for FOCUS managed JOINs, but it doesn't hurt, right?

quote:

SET ALL is of course only for focus managed joins. It has no bearing on sql and requests processed in it's entirety by the host (r)dbms.

Not exactly true. It used to be this way but it is not now. When WebFOCUS translates a request to SQL, SET ALL=OFF and SET ALL=ON will create 2 different SQL requests. For example:

 JOIN RF_REFER_ID IN KDSREFER TO VS_REFER_ID IN KDSNVSTG
-*
 TABLE FILE KDSREFER
 PRINT RF_REFER_ID RF_REFER_DT VS_BEGDT
 WHERE RF_REFER_DT GE '20090201'
 ON TABLE HOLD
 END

creates the following SQL:

 (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 SELECT T1."REFER_ID",T1."REFER_DT",T2."BEGIN_DT" FROM
 KIDSPROD.TREFERRAL T1,KIDSPROD.TINVESTIGATION T2 WHERE
 (T2."REFER_ID" = T1."REFER_ID") AND (T1."REFER_DT" >=
 TO_DATE('01-02-2009','DD-MM-YYYY'));
 1
 0 NUMBER OF RECORDS IN TABLE=    28892  LINES=  28892
 0

Now, if I add SET ALL=ON, I get the following:

 JOIN RF_REFER_ID IN KDSREFER TO VS_REFER_ID IN KDSNVSTG
-*
 SET ALL=ON
-*
 TABLE FILE KDSREFER
 PRINT RF_REFER_ID RF_REFER_DT VS_BEGDT
 WHERE RF_REFER_DT GE '20090201'
 ON TABLE HOLD
 END

creates the following SQL

 (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 SELECT T1."REFER_ID",T1."REFER_DT",T2."REFER_ID",T2."BEGIN_DT"
 FROM KIDSPROD.TREFERRAL T1,KIDSPROD.TINVESTIGATION T2 WHERE
 (T2."REFER_ID" (+) = T1."REFER_ID") AND (T1."REFER_DT" >=
 TO_DATE('01-02-2009','DD-MM-YYYY'));
 1
 0 NUMBER OF RECORDS IN TABLE=    64439  LINES=  64439
 0

The first one return only rows where there are rows in both the parent and child. The second returns all the parent rows.

Rick Man, I suggest that you leave SET ALL=OFF as the default and train the programmers on how it works unless you always want the parent even if there are no child rows, otherwise you have to remember when to SET ALL back to OFF. Pick your poision.

We leave SET ALL as OFF and will use the ON TABLE SET ALL ON syntax when we want the parent even if no child rows exist. For really complex JOINS we will code our own SQL.

One other note, I could have written the code this way and the SET ALL value makes no difference:

 JOIN VS_REFER_ID IN KDSNVSTG TO RF_REFER_ID IN KDSREFER
-*
 TABLE FILE KDSNVSTG
 PRINT RF_REFER_ID RF_REFER_DT VS_BEGDT
 WHERE RF_REFER_DT GE '20090201'
 ON TABLE HOLD
 END

 (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 SELECT T1."REFER_ID",T1."BEGIN_DT",T2."REFER_ID",T2."REFER_DT"
 FROM KIDSPROD.TINVESTIGATION T1,KIDSPROD.TREFERRAL T2 WHERE
 (T2."REFER_ID" = T1."REFER_ID") AND (T2."REFER_DT" >=
 TO_DATE('01-02-2009','DD-MM-YYYY'));
 1
 0 NUMBER OF RECORDS IN TABLE=    28892  LINES=  28892
 0

The problem here is that the procedure takes longer to run as I am now reading every row in the Investigation table and every matching row in the Referral table, then keeping the records I want. (There is an index on referral date.)

This illustrates one of the problems with using a language like WebFOCUS/FOCUS with relational database. While one can code something multiple ways to get the same answer set from the database, one way may be vastly superior to the other when talking about performance.

Hope this helps.

No, he means used with a TABLE request rather than a SQL passthru.

You beat me to it jg with a much more detailed answer.

JG thanks for the example. That makes sense and is what I thought.
Any thoughts on SET ALL=PASS

It has been my experience that using SET ALL=PASS has no effect on relational tables.

I understand that SET ALL=PASS basically says that if a child record is not present, assume that the child record PASSes any screening tests on that segment/table and return the parent.

For example:

 JOIN RF_REFER_ID IN KDSREFER TO VS_REFER_ID IN KDSNVSTG
-*
 TABLE FILE KDSREFER
 PRINT RF_REFER_ID RF_REFER_DT VS_BEGDT
 WHERE VS_BEGDT GE '20090201'
 ON TABLE HOLD
 END

returns

(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 SELECT T1."REFER_ID",T1."REFER_DT",T2."BEGIN_DT" FROM
 KIDSPROD.TREFERRAL T1,KIDSPROD.TINVESTIGATION T2 WHERE
 (T2."REFER_ID" = T1."REFER_ID") AND (T2."BEGIN_DT" >=
 TO_DATE('01-02-2009','DD-MM-YYYY'));
 1
 0 NUMBER OF RECORDS IN TABLE=    28737  LINES=  28737
 0

while

 JOIN RF_REFER_ID IN KDSREFER TO VS_REFER_ID IN KDSNVSTG
-*
 SET ALL=PASS
-*
 TABLE FILE KDSREFER
 PRINT RF_REFER_ID RF_REFER_DT VS_BEGDT
 WHERE VS_BEGDT GE '20090201'
 ON TABLE HOLD
 END

returns exactly the same thing even with the outer join:

 (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 SELECT T1."REFER_ID",T1."REFER_DT",T2."REFER_ID",T2."BEGIN_DT"
 FROM KIDSPROD.TREFERRAL T1,KIDSPROD.TINVESTIGATION T2 WHERE
 (T2."REFER_ID" (+) = T1."REFER_ID") AND (T2."BEGIN_DT" >=
 TO_DATE('01-02-2009','DD-MM-YYYY'));
 1
 0 NUMBER OF RECORDS IN TABLE=    28737  LINES=  28737
 0

If SET ALL=PASS had any effect, I would expect several hundred thousand more rows returned because that is how many rows there are in the referral table that do not have a corresponding row in the investigation table.

To get the answer set I expect with SET ALL=PASS, I would have to do a MATCH FILE or do my own SQL:

SELECT T1."REFER_ID",T1."REFER_DT",T2."REFER_ID",T2."BEGIN_DT"
 FROM KIDSPROD.TREFERRAL T1,KIDSPROD.TINVESTIGATION T2 WHERE
 (T2."REFER_ID" (+) = T1."REFER_ID") AND (T2."BEGIN_DT" (+) >=
 TO_DATE('01-02-2009','DD-MM-YYYY'));

This message has been edited. Last edited by: jgelona,

Thanks