Left/Outer JOIN

[pvuppa1]

How do i specify the left or right outer joins in wf?

 

[gizzy17]

SET ALL=ON will produce a left outer join.

I don't think there is anything in FOCUS that will produce a right outer join. To get the same results though you would use MATCH FILE with the NEW option. Personaly, I would reverse the join and use the ALL = ON option.

 

[gizzy17]

Oh - one more thing.

SET ALL=PASS will also produce a left outer join but ignore the selection criteria for your guest table if no record exists.

 

[pvuppa1]

Outer join is not working evenwhen i say
SET ALL=ON, it just gives me the same records as if an inner join.
I checked the record count/records affected on my sql database for the outerjoin and it is different.

im using webfocus 4.3.6
is it SET ALL=ON or -SET ALL=ON ?
I tried both anyway and ended up with no luck.

 

[pvuppa1]

Gizzy17,
Just to give you an idea of what I did so far.
IN MY DATABASE:

SELECT count(*) FROM eUser LEFT OUTER JOIN eAttribute ON eUser.eUserName = eAttribute.eUserName...gives me a count (103)
SELECT count(*) FROM eUser right OUTER JOIN eAttribute ON eUser.eUserName = eAttribute.eUserName..gives me a count (103)

IN MY FEX PROCEDURE:

JOIN EUSER.EUSERNAME IN eUser TO ALL EATTRIBU.EUSERNAME IN eAttribute AS JOIN0
SET ALL = PASS(tried both ON, PASS)
TABLE FILE EUSER
SUM
CNT.EUSERNAME AS 'Case,Count'...gives me a count (64)
END

I thought it should produce the same record counts both by a DB join and a WebFocus JOIN..I feel stupid and confused (

 

[gizzy17]

Try adding

SQL [dbEngine] SET OPTIMIZATION ON

to the begining of the request. I beleive that focus is trying to handle the join itself by retrieving the records from the two tables seperately.