The Left join using where conditions behaves as a inner join.It filters out the rows at he end instead of keeping the source row ans adding null columns for non matching condition.
WF8105M windows 10This message has been edited. Last edited by: FP Mod Chuck,
October 24, 2018, 02:12 PM
BabakNYC
What is the DBMS? If it's an SQL database, run your request with SQL Trace on so you can see what kind of SELECT it's generating.
WebFOCUS 8206, Unix, Windows
October 25, 2018, 03:29 PM
Hallway
The following works for me:
SET HOLDLIST = PRINTONLY
SET ASNAMES = SUBST
SET PAGE-NUM = OFF
TABLE FILE car
SUM SALES AS 'SALES1'
BY CAR
WHERE CAR NE 'ALFA ROMEO'
ON TABLE HOLD AS HOLD1
END
-RUN
TABLE FILE car
SUM SALES AS 'SALES2'
BY CAR
WHERE CAR NE 'BMW'
ON TABLE HOLD AS HOLD2
END
-RUN
JOIN LEFT_OUTER FILE HOLD1 AT HOLD1.HOLD1.CAR
TO UNIQUE FILE HOLD2 AT HOLD2.HOLD2.CAR TAG J001 AS J001
WHERE HOLD1.HOLD1.CAR EQ J001.HOLD2.CAR;
WHERE J001.HOLD2.CAR NE 'AUDI';
END
TABLE FILE HOLD1
SUM
HOLD1.HOLD1.SALES1
J001.HOLD2.SALES2
BY HOLD1.HOLD1.CAR
END
Finaloutput:
CAR SALES SALES2
AUDI 7800 0
BMW 80390 0
DATSUN 43000 43000
JAGUAR 12000 12000
JENSEN 0 0
MASERATI 0 0
PEUGEOT 0 0
TOYOTA 35030 35030
TRIUMPH 0 0
HOLD1 is the main table and has ALFA ROMEO filtered out and it doesn't appear anywhere in the final output
HOLD2 is the joined table and has BMW filtered out and the SALES2 field in the final report is 0 in the final output
In the join, HOLD2 has AUDI filtered out in the WHERE clause of the join and SALES2 for AUDI is 0 in the final output
This message has been edited. Last edited by: Hallway,
Hallway
Prod: 8202M1
Test: 8202M4
Repository:
OS:
Outputs:
October 30, 2018, 01:48 PM
Thomas Raspberry
Hi Siva, Yep, you are not the only having similar results using LEFT OUTER join. You need to have following line at top of your FEX code for LEFT to work: SET ALL = PASS
WebFOCUS 8.201
October 31, 2018, 09:48 AM
David Briars
quote:
It filters out the rows at the end instead of keeping the source row and adding null columns for non matching condition.
Sounds like you are using one of the relational engines?
If that is the case, if you want WebFOCUS to assign null values to the fields in a missing segment instance when a left outer join is in effect, you can issue the command SET SHORTPATH=SQL.