If you have tables A and B and common field FLD and want to find cases where no B exists for A then

JOIN LEFT_OUTER FLD IN A TO FLD IN B AS J1
TABLE FILE A
PRINT A.FLD
WHERE B.FLD EQ MISSING
END

does not work.

What happens is that the wf adapter reasons that any WHERE test on B does not require an outer join since the missing value retrieved will always fail the test.

This is almost true but it will pass the IS NULL test above!!!

SET ALL=PASS to get round this. (This also makes INNER JOINs into OUTER JOINs!)


Again this seems to be an unholy compromise of the original FOCUS behaviour and rdbms behaviour. This is the single most confusing element and I would say that this is a great deal of the feeling that the core product does not work.

What's needed is something like
SET JOINBEHAVIOUR=RDBMS|FOCUS
SET NULLBEHAVIOUR=RDBMS|FOCUS

Where the whole of wf table behaves in an RDBMS way!

WF is the better more logical way but there are more RDBMS's out there.

<<< End of Rant >>>

John,

Better still use SQL passthru to achieve exactly what you want without having to go around the WF code limitations. Make the RDBMS do all the work and control the environment.

T