Hi All, I am struggling with using JOIN function in WebFocus report to get result as left outer join. No matter what I do, I always get result as inner join. Is there a syntax format to be used?
My code is as such:

JOIN
REGION_ID IN USER_DATA TO UNIQUE
REGION_ID IN DIM_REGIONS TAG J1 AS J1
END

TABLE FILE USER_DATA
BY USER_DATA.REGION_ID
BY J1.REGION
SUM UNITS\I8C

I would like this report to show all Regions in User Data that are both, matched and not matched to DIm_Regions.

Any suggestions?

Put this line on top of the JOIN section
SET ALL =ON
or
SET ALL =PASS to include the parent records irrespective of the filter conditions..


thanks
Sashanka

You may have to turn LEFT OUTER JOIN capability on, depending on the DBMS:

SQL SQLORA SET SQLJOIN OUTER ON
END

...

SQL DB2 SET SQLJOIN OUTER ON
END

I've also used

SET SHORTPATH = SQL

Change the JOIN syntax:

JOIN LEFT_OUTER CONTRACT_ID IN TEST_CONTRACT TO MULTIPLE CONTRACT_ID IN XJM AS J1

Anna,
What type are your files USER_DATA and DIM_REGIONS? SQL? FOCUS? Sequential?

Thank you all for comments and tips.

Daniel, the files are a mix; there could be a left join from csv (raw) data to a DB2 table or form db2 table to another db2 table.