The above tables shows all permutations of WebFOCUS join for 2 relational tables of the same type with full optimization on so the join should be passed to the rdbms for evaluation rather than it being a record by record retrieval (WebFOCUS managed JOIN).

The actual adapter used was WebFOCUS 762 Oracle adapter.

The 2 tables involved were parenttable and childtable and their columns are parentkey, childkey and childfield.

The combinations the following WebFOCUS syntax are ennunciated in the above diagram therefore carrying out every possible equijoin (not the new conditional join)

SET ALL={OFF|ON|PASS}
JOIN [INNER|LEFT_OUTER] parentkey IN parenttable TO [UNIQUE|ALL|MULTIPLE] childkey IN childtable AS J0
TABLE FILE parenttable
PRINT [ALL.]parentkey childkey
[IF childfield {EQ value |IS MISSING}]
END

Note the crucial inclusion of the IS MISSING test alongside the EQ value. For rdbms the EQ value test will always devalue a LEFT OUTER join to an INNER JOIN whereas the IS MISSING will not cause this to happen thus allowing us to see the parents without children. As I have pointed out many times wf does not realise this distinction!

The following is a list of the expected SQL that the combinations of the query will generate. These are refered to in the 'Expected' and 'Actual' columns of the 3 tables.

(1A) SELECT parentkey, childkey
FROM parenttable, childtable 
WHERE parentkey = childkey 

(1B) SELECT parentkey, childkey
FROM parenttable, childtable 
WHERE parentkey = childkey 
AND childfield = value 

(1C) SELECT parentkey, childkey
FROM parenttable, childtable 
WHERE parentkey = childkey 
AND childfield IS NULL

(2A) SELECT parentkey, childkey
FROM parenttable LEFT OUTER JOIN  childtable 
ON  parentkey = childkey 

(2B) SELECT parentkey, childkey
FROM parenttable LEFT OUTER JOIN  childtable 
ON  parentkey = childkey 
WHERE childfield = value 

(2C) SELECT parentkey, childkey
FROM parenttable LEFT OUTER JOIN  childtable 
ON  parentkey = childkey 
WHERE childfield IS NULL

(3) SELECT parentkey, childkey
FROM parenttable LEFT OUTER JOIN  childtable 
ON  parentkey = childkey AND childfield = value 

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

Clearly on 8 occasions the webfocus syntax does not translate as the manual describes.
I have reported these serious failings on numerous occasions and IBI have failed to do anything about them (either a change in the manual or fixing the underlying code).
The table is thus presented as an online assist as to which wf Joins will produce the correct rdbms translations.

The failings can be summarised

1. The newer LEFT_OUTER and INNER syntax is NOT independent of the older SET ALL={OFF|ON|PASS} syntax.
2. There is only one case where a test on a child outer join works

SET ALL=PASS
JOIN [INNER|LEFT_OUTER] parentkey IN parenttable TO [UNIQUE|ALL|MULTIPLE] childkey IN childtable AS J0
TABLE FILE parenttable
PRINT parentkey childkey
IF childfield IS MISSING
END

The ALL=PASS syntax is crucial here - note the combinations that may be used in the JOIN even the illogical inner join.

3. To achieve a JOIN that is the equivalent of ALL=PASS the equivalent relational syntax is

SELECT parentkey, childkey
FROM parenttable LEFT OUTER JOIN  childtable 
ON  parentkey = childkey AND childfield = value 

In webfocus the effect can be achieved using the new conditional join

JOIN LEFT_OUTER FILE parenttable AT parentkey TO MULTIPLE FILE childtable AT childkey AS J1
WHERE parentkey = childkey AND childfield = value ;
END 
TABLE FILE parenttable
PRINT parentkey childkey
END

Note that the WHERE clause is now contained as part of the JOIN clause.

However the likelihood is that the conservative adapter will downgrade this JOIN into a wf managed Join and thus performance will suffer.

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

Based on your finding that you outlined in Hottrack case 40462527 and after lengthy discussions with engineering, we are implementing, in release 7.7, SQL style JOIN semantics using a new value for the SET ALL= parameter. This new setting will allow testing on NULL, not NULL and any combination of predicates against the target file.
This new setting is valid for any supported adapter and not just SQL data sources.
The setting applies to both joins passed to SQL and the joins executed by the Focus engine.

Note: The SQL shown in the following examples may change based on other factors such the SQL engine ability to execute the request in one SQL statement, for example, join of SQL tables to Webservices. However the resulting report will be correct.

In release 7.7 the suggested syntax to report against a JOIN’ed structure is as follows:-

SET ALL = SQL
JOIN
LEFT_OUTER | INNER COLA IN A TO COLB IN B AS J1
END
TABLE FILE A
PRINT …
WHERE COLB EQ ‘value’
[WHERE COLB IS MISSING]
[WHERE COLB EQ ‘value’ OR COLB IS MISSING]
END

Base data used for examples

EMPLOYEE table with EMP_NAME and DEPT_ID
DEPARTMENT table with DEPT_ID and DEPT_NAME

PAGE 1

DEPT_ID EMP_NAME DEPT_ID DEPT_NAME
31 rafferty 31 sales
33 jones 33 engineering
33 steinberg 33 engineering
34 smith 34 clerical
34 robinson 34 clerical
36 jasper . .


Example 1 (WHERE target_column = value)

SET ALL=SQL
-RUN
JOIN
LEFT_OUTER DEPT_ID IN EMPLOYEE TAG T1 TO DEPT_ID IN DEPARTMENT TAG T2 AS J1.
END
-RUN
TABLE FILE EMPLOYEE
PRINT T1.DEPT_ID EMP_NAME T2.DEPT_ID DEPT_NAME
WHERE T2.DEPT_ID EQ 31;
END

SQL generated

SELECT T1."EMP_NAME",T1."DEPT_ID",T2."DEPT_NAME",T2."DEPT_ID"
FROM ( ARH.dbo.EMPLOYEE T1 LEFT OUTER JOIN ARH.dbo.DEPARTMENT
T2 ON T2."DEPT_ID" = T1."DEPT_ID" ) WHERE (T2."DEPT_ID" = 31);

Query result

PAGE 1

DEPT_ID EMP_NAME DEPT_ID DEPT_NAME
31 rafferty 31 sales

Example 2 (WHERE target_column IS NULL)

SET ALL=SQL
JOIN
LEFT_OUTER DEPT_ID IN EMPLOYEE TAG T1 TO DEPT_ID IN DEPARTMENT TAG T2 AS J1.
END
TABLE FILE EMPLOYEE
PRINT T1.DEPT_ID EMP_NAME T2.DEPT_ID DEPT_NAME
WHERE T2.DEPT_ID IS MISSING;
END

SQL generated

SELECT T1."EMP_NAME",T1."DEPT_ID",T2."DEPT_NAME",T2."DEPT_ID"
FROM ( ARH.dbo.EMPLOYEE T1 LEFT OUTER JOIN ARH.dbo.DEPARTMENT
T2 ON T2."DEPT_ID" = T1."DEPT_ID" ) WHERE T2."DEPT_ID" IS NULL;

Query result

PAGE 1

DEPT_ID EMP_NAME DEPT_ID DEPT_NAME
36 jasper . .

Example 3 (Combination of the above two using OR)

SET ALL=SQL
JOIN
LEFT_OUTER DEPT_ID IN EMPLOYEE TAG T1 TO DEPT_ID IN DEPARTMENT TAG T2 AS J1.
END
TABLE FILE EMPLOYEE
PRINT T1.DEPT_ID EMP_NAME T2.DEPT_ID DEPT_NAME
WHERE T2.DEPT_ID IS MISSING OR T2.DEPT_ID EQ 31;
END

SQL generated

SELECT T1."EMP_NAME",T1."DEPT_ID",T2."DEPT_NAME",T2."DEPT_ID"
FROM ( ARH.dbo.EMPLOYEE T1 LEFT OUTER JOIN ARH.dbo.DEPARTMENT
T2 ON T2."DEPT_ID" = T1."DEPT_ID" ) WHERE ((T2."DEPT_ID" IS
NULL) OR (T2."DEPT_ID" = 31));

Query result

PAGE 1

DEPT_ID EMP_NAME DEPT_ID DEPT_NAME
31 rafferty 31 sales
36 jasper . .

Tony

Thanks for your reply.

This will work - in effect you are making INNER and LEFT_OUTER joins independent of the SET ALL setting. This was previously promised and wrongly implemented.

I guess you are saying you can't fix the wrong implementation because of the existing codebase, and are thus introducing this feature again - hopefully properly implemented this time.

Also regarding that this feature will apply to wf managed join, I have question.

1. Will TO UNIQUE be implemented as INNER rather than the current LEFT_OUTER default?

2. Will INNER or LEFT_OUTER be required or is INNER the default?

Again I can see this is a one time solution and it makes sense to package it all together so you can say that wf obeys relational join syntax. I assume the recommendation will be for new sites to adopt this setting as default.

I do think however this should be made available in 76 - since it is still effectively a bug fix packaged as a NFR.

As a general point thank you for taking the time to explain yourself on the forum - I know myself the pressure is on to code rather than communicate more with our clients - but I do think it should be encouraged for IB developers to get out there and see what's going on in terms of use of their product.

John

Sob! Too bad Left Outer Joins don't work in v7.6.5 when there is selection criteria on the cross-referenced table.

We're probably not upgrading to v7.7 for years.