My outer joins are behaving like inner joins.
Are outer joins supported in focus 7.6 for mainframe?

This message has been edited. Last edited by: <Emily McAllister>,

You can find details on JOINing in the manual called Creating Reports.

Here is an example.

Creating a Left Outer Join
The following JOIN command creates a left outer join between the EMPINFO data source
and the EDINFO data source:
JOIN CLEAR *
JOIN LEFT_OUTER EMP_ID IN EMPINFO TO MULTIPLE EMP_ID IN EDINFO AS J1
The following request displays fields from the joined structure:
TABLE FILE EMPINFO
PRINT LAST_NAME FIRST_NAME COURSE_NAME
END

Thanks I have read that documentation.
**I am talking about the sql translator, NOT STD FOCUS JOIN LOGIC***

However my left joins are being translated into inner joins.
If i try to left join to a subquery, i receive an error that outer joins are not supported.
Are they supported in 7.6?
If not why is focus not erroring when I issue a left outer join but changing to inner join?
It seems ridiculous

SQL
SELECT *
FROM TABLE1
LEFT JOIN TABLE2
ON SOMECONDITION..
END

** FOCUS IS TRANSLATING THE ABOVE INTO AN INNER JOIN **

Anyone know why?
I thought i had seen some strange implementations of ANSI SQL joins... but this is just lunacy

Jashwood - WebFOCUS v7.6 is quite old. This syntax works in 8007.
The SQL Passthrough section is the SQL WebFOCUS created under the covers and sent to SQL Server when I ran the included WebFOCUS report.
I turned on a trace and captured the SQL created. I then pasted it into a SQL Server query to confirm it ran.

-******** SQL Passthrough *********
ENGINE SQLMSS SET DEFAULT_CONNECTION SNAKE
SQL SQLMSS PREPARE JOINTEST FOR
SELECT TOP 1000
 T1."BUSINESS_UNIT",
 T1."PROJECT_ID",
 T1."CONTRACT_NUM",
 T1."BI_BUDGET_YEAR",
 T1."BI_PR_BUDGET_YEAR",
 SUM
   (T2."FA_INST_RATE_PCT") AS 'FA_INST_RATE_PCT',
    AVG(T2."FUNDED_AMT") AS 'FUNDED_AMT'
 FROM
 ( FMSIBIDEV.dbo.BI_PROJ_BY_HDR T1
 LEFT OUTER JOIN FMSIBIDEV.dbo.BI_PROJ_BY_DIM T2
 ON T2."PROJECT_ID" = T1."PROJECT_ID" AND
 T2."CONTRACT_NUM" = T1."CONTRACT_NUM" AND
 T2."BI_BUDGET_YEAR" = T1."BI_BUDGET_YEAR" )
 GROUP BY
 T1."BUSINESS_UNIT",
 T1."PROJECT_ID",
 T1."CONTRACT_NUM",
 T1."BI_BUDGET_YEAR",
 T1."BI_PR_BUDGET_YEAR"
 ORDER BY
 T1."BUSINESS_UNIT",
 T1."PROJECT_ID",
 T1."CONTRACT_NUM",
 T1."BI_BUDGET_YEAR",
 T1."BI_PR_BUDGET_YEAR";
END
-***************************************
-* Original WebFOCUS Report Which Created SQL Passthrough code ***********
JOIN
LEFT_OUTER BI_PROJ_BY_HDR.BI_PROJ_BY_HDR.PROJECT_ID
AND BI_PROJ_BY_HDR.BI_PROJ_BY_HDR.CONTRACT_NUM
AND BI_PROJ_BY_HDR.BI_PROJ_BY_HDR.BI_BUDGET_YEAR IN BI_PROJ_BY_HDR TO MULTIPLE
BI_PROJ_BY_DIM.BI_PROJ_BY_DIM.PROJECT_ID
AND BI_PROJ_BY_DIM.BI_PROJ_BY_DIM.CONTRACT_NUM
AND BI_PROJ_BY_DIM.BI_PROJ_BY_DIM.BI_BUDGET_YEAR IN BI_PROJ_BY_DIM TAG J0 AS J0
END
TABLE FILE BI_PROJ_BY_HDR
SUM
     FA_INST_RATE_PCT
     AVE.FUNDED_AMT AS 'AVE,FUNDED_AMT'
BY  LOWEST BUSINESS_UNIT
BY  LOWEST PROJECT_ID
BY  LOWEST CONTRACT_NUM
BY  LOWEST BI_BUDGET_YEAR
BY  LOWEST BI_PR_BUDGET_YEAR
WHERE RECORDLIMIT EQ 1000
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

I found this. It may be useful for your problem.

In release 7.7, a new setting is added:

SET ALL=SQL

This new setting allows the outer join syntax to be sent to SQL and the SQL.
is optimized.

Prior to release 7.7, using WHERE TOTAL instead of WHERE will send the left
outer join to the SQL.

I hope this helps.

Hey Guys,
Thanks for your responses - I'm actually trying to leverage the internal SQL engine, when it comes to passthrough I am simply writing SQL myself.

These objects are WebFOCUS hold files and i'm trying to join them together using SQL syntax which is apparently supported. The manual I'm reading simply states "WebFOCUS 7", but I'm assuming it is for the latest release of version 7.

I'm going to revert to using MATCH FILE, or conditional JOINs the old style FOCUS way.

Thanks again for getting back to me, much appreciated

Thanks,
Josh